Free Microsoft Excel 2013 Quick Reference

Sumif function for non consecutive cells in sum and criteria

I'm having issues figuring out how to setup a sumif function with a range that is not consecutive. For each day we have a new column, so setting up a named range is not optimal, a formula that could essentially be cut and pasted to each new day would be the only solution that would save any time.

I need to add cells EK6, EK11, EK16, EK21...EK56, EK61 if they have a value of 2 in the cell above it, being EK5, EK10, EK15, EK20...EK55, EK60 respectively. Is there any way to setup a reusable formula to accomplish this task?


Post your answer or comment

comments powered by Disqus
Question: If my data is NOT in a contine cell, how can I run the formula? I can't get the Non-Continue Cell formula to work.

This formula does not work if the data cells are NOT continue:

=SMALL(A1:A100,COUNTIF($A$1:$A$100,0)+1)

What if my data are in cells A2, A6, C7, E9

How can I look up the Min function for these 4 cells: A2, A6, C7, E9 and exclude Zero ?

I need to look up the smallest value for these 4 cells while exclude zero.

Please help.

Getting to Know Excel's SUMPRODUCT and SUMIF Functions - For Dummies ...

Without any doubt, SUM is the preeminent function in the Math & Trig category of Excel functions. This function is so central to spreadsheet formula-making that the Standard ...

Hi, folks.

I'm attempting to create a spreadsheet that contains links to non-consecutive cells in another worksheet. Let me elaborate. If I put "=A1" in cell B1 and then copy that down, it will update the relative formula consecutively, i.e. B2=A2, B3=A3, B4=A4, B5=A5, etc.". Yeah, that's elementary.

What I need to do is find a way to do the same thing, but to increase the resulting link.....so that if I copied the formula down column B I would get: B1=A1, B2=A5, B3=A9, B4=A13 (in which the row value of "A" gets 4 added to it for each single row change of B.)

So we all know that a formula like =A1+4 is going to add the value of A1 to 4 instead of increasing the row value of A1 to A5. Is there a way to do this? Otherwise, I'll have to manually change every cell reference for all my formulas beyond row 1.....tedious? You bet.

Just so you know, I have looked into vlookup, indexing, and matching and they will not do what I need. The values I need to reference are on different rows than the only value I can use for the lookup. So if the value in cell A1 satisfies the data I need, it will return cell K11....something like that. Also, there are several other data items I have to reference so I can't add additional if statements to give each line a unique value. I have too many data items per section and there is the limit of seven nested "if's"

I have yet to find anything in Excel that will do something like this which I think should be fairly simple (or at least should have been added as a standard option). Any ideas?

Hi all,

I have no idea where to start. I need a macro that will search for non empty values in a column and when it finds any value put a border on top of that row. This is done to distingish between different records. So for example:

1. Record_One New_York
2. Washington3. Record_Two California
4. New_Jersey 5. Record_Three Florida

If given this table the macro would run it will put a border on top of Rows 1, 3, 5 to distingish the different sets of states. Please help.

Thank You

Hey,

I am trying to write a function for a cell that counts non-contiguous cells in a column and then sums them together. The issue is this cell will be copied to multiple worksheets and will have to re-count all the cells when it is copied.

This is my first time writing a function.

Thanks in advance

I have a spreadsheet in the following form:

            Col1   Col2   Col3   Col4   Col5   Col6   Col7  
Col8
Row1        10                   30            50
Row2        50     40     30     20     30
Result      60                   90            30
I'd like to sum Row2/Cols2~4 based on a) Row1/Col4 having a value and b) the first preceding non-blank column in Row1 is Col1. Thus, the Result for Col4 would be 90 (40+30+20).

I believe I need a function to determine the cell address of the previous non-blank cell in Row1 in order to sum Row2 from that endpoint.

Any suggestions for how do to this?

Hello,

I am trying to average two non-consecutive cells with running formulas in them. One of the cells has the data to finish the running formula, but the other cell does not have the data to finish the formula. I want the equation to average both cells, but I only want the average to count the cell if the running formula has been finished. See below:
Reading at Lock (mV)Strain Gage-1Strain Gage-218.9Strain Gage-318.65Strain %Strain Gage-10.000Strain Gage-21.208Strain Gage-31.192Strain Gage Ave.0.800 (Eq=sum(D38:D40)/countif(D38:D40,"0") COMPARISONS:SG 1 vs. 2 Ave.SG 1 vs. 3 Ave.SG 2 vs. 3 Ave.RequirementPair Averages (% Strain)1.208 (Eq=sum(D38:D39)/countif(D38:D39, "0")#VALUE! (Eq=sum(D38,D40)/countif((D38,D40),"0")1.200 (Eq=sum(D39:D40)/countif(D39:D40,"0")

We do not have the Strain Gage -1 mV reading, therefore the running formula in the % Strain for Strain Gage-1 is not complete. However, I need the spreadsheet to determine the % Strain average of SG-1 and SG-3. See green column with a return of #VALUE! Due to the non-consecutive nature of the cells D38 and D40, Excel won't let me use the same averaging formulas that worked for the other two green cell averages. Please advise. Thanks!

Hi

In MS Excel 2007, I'm trying to use a function to search for (and display) a
specific text string that resides within a cell, providing that it is the
last non blank cell in a range of cells across a row.

e.g. If searching row 2:2 (or a section of this row) for the last non blank
cell, I want to then search within that last cell to locate the word "pass",
where the same cell may also contain any combination of text/number values.
If "pass" exists within the cell, I want to return a the same text value of
"pass". Note that “pass” may exist across various cells in the range.

-- I've managed to successfully return the entire contents that reside
within the last non-blank cell (with the following formula):

=IF(ISNA(LOOKUP(2,1/('QA Results'!$D2:$CY2""),'QA
Results'!$D2:$CY2)),"",LOOKUP(2,1/('QA Results'!$D2:$CY2""),'QA
Results'!$D2:$CY2))

-- I've also achieved similar results when searching the cell range for the
last known cell that successfully contains the text “pass” (this is not
necessarily the last non blank cell in the range). Again the entire cell
content is returned.

=IF(OR(ISNA('QA Results'!D2:CY2),ISERROR(LOOKUP(2,1/SEARCH("Pass",'QA
Results'!D2:CY2),'QA Results'!$D$2:$CY$2))),"",LOOKUP(2,1/SEARCH("Pass",'QA
Results'!D2:CY2),'QA Results'!$D$2:$CY$2))

Any assistance would be appreciated.

I need to use COUNTIF function for a range of cells that are not adjacent to one another. The formula I'm using is as follows:- =COUNTIF(D13:E13:G13:J13,8)

I was hoping this would return the total number of cells where the value in that cell was 8. However, it returns total number for cells in the range between D13 and J13.

Do I need to use another function or just change the above. Thanks.

How can I do a countif in non-consecutive cells over a series of rows,
like evens and odds?. I tried this:
=COUNTIF($C$1:$Z$1:$C$3:$Z$3:$C$5:$Z$5:$C$7:$Z$7:$ C$9:$Z$9:$C$11:$Z$11:$C$13:$Z$13,"1")
but it counts everything on the even rows as well.

Thanks to all.

is there a way to tell excel to sum the next two non-empty cells in a range?

A1 = 5
A2 = ""
A3 = ""
A4 = 4
A5 = ""

A1 = 5
A2 = 4
A3 = ""
A4 = 7
A5 = ""

if i wanted to evaluate A1:A5, and add only the first two non-empty cells,
so either way, the formula would return the result of 9. TIA.

I have used the SUMIF function many times on worksheets that have the same
type of information in a "range". Now I need to use the SUMIF function in a
spreadsheet where the "ranges" are not adjacent to each other. For example
in range J1 to J289 I only need every third numbered row for the SUMIF
function. I have Named the range using every third row, but when I try to
insert the Named range into the SUMIF formula, I always get an error.
My range name looks like this j1,j4,j7,j11 etc. These are rows I want to
SUMIF.

Please help: How can i programmatically lookup the cell adress for next
non-blank cell in a range?

Toreadore

--
toreadore
------------------------------------------------------------------------
toreadore's Profile: http://www.excelforum.com/member.php...o&userid=35850
View this thread: http://www.excelforum.com/showthread...hreadid=556407

I'm looking for a function or formula in Excel 2003 which will return the
last non-blank cell in a range. The values in the range are numeric;
however, they're not sorted in numeric order. I could probably figure out
how to write a macro, but I'd prefer a formula. Any suggestions?

Thanks.

How can I do a countif in non-consecutive cells over a series of rows,
like evens and odds?. I tried this:
=COUNTIF($C$1:$Z$1:$C$3:$Z$3:$C$5:$Z$5:$C$7:$Z$7:$C$9:$Z$9:$C$11:$Z$11:$C$13:$Z$13,"1")
but it counts everything on the even rows as well.

Thanks to all.

I have an excell sheet 50 rows by 8 columns, all numbers between 0 and 15. I need to add the 4 highest numbers out of 5 non-consecutive cells. e.g.: say numbers from A1:H1 are 0,1,15,1,15,9,15,3. I need to add the 4 highest numbers out of the five cells: A1,B1, D1, G1 and H1. Should be 20. Tried: =SUM(LARGE(a1,B1,D1,G1,H1,{1,2,3,4})) but excell barfs on the non-consecutive cells. Any way to do this?
Thanks,
Bill

I have the number of a column in a variable “ab”
How do I write the VB statement to have the CountA function count the non-blank cells in the Column?

Range("A22").Select
ActiveCell = "=COUNTA(???:???)"

Or is there another way to do it with VB?
Thanks for any help you can give me.

Please help: How can i programmatically lookup the cell adress for next non-blank cell in a range?

Toreadore

Greetings,

I've a spreadsheet with multiple worksheets.

I'm looking to display a sequence of data in worksheet 1, based on data present in worksheet 2. Say for example my data is as follows:

Worksheet 2:

Cell A1=6
Cell A6=7
Cell A11=8

Worksheet 1:
A B
1 2001
2 2002
3 2003

Is there a way that I can insert to display in consecutive cells in worksheet 1, data from equal intervals (but not from consecutive cells) from worksheet 2?

E.g. take value from every 5th row of column A in worksheet 2, and display it in rows 2, 3, 4 etc.....in worksheet 1?

I've attached a sample of what I mean and how I wish for it to be displayed as the end result.

Any help much appreciated.

I have data in a range of D11:K11 and its sum in cell L11. This goes down to around 30 rows. Sometimes I have to hide columns in range of D11:K11 and want to sum the non hidden cells in L11 and down to 30 rows.
Is there any formula or function???

I'm looking for a function or formula in Excel 2003 which will return the
last non-blank cell in a range. The values in the range are numeric;
however, they're not sorted in numeric order. I could probably figure out
how to write a macro, but I'd prefer a formula. Any suggestions?

Thanks.

is there a way to tell excel to sum the next two non-empty cells in a range?

A1 = 5
A2 = ""
A3 = ""
A4 = 4
A5 = ""

A1 = 5
A2 = 4
A3 = ""
A4 = 7
A5 = ""

if i wanted to evaluate A1:A5, and add only the first two non-empty cells,
so either way, the formula would return the result of 9. TIA.

In the column I have both user entered and calculated values, how do I sum
the values of non calculated cells...?

I am trying to count the number of non-blank cells in columns B-F and paste the value in column H. I am trying to define the range for each row but can't get the syntax correct. Any help appreciated. Thanks.

Sub aa()

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

    For x = 2 To LastRow
        Agents = WorksheetFunction.CountA(Range("B" & x).("F" & x)
        Range("H" & x) = Agents
    Next

End Sub



No luck finding an answer? You could always try Google.