Free Microsoft Excel 2013 Quick Reference

Summing non consecutive cells

I am attempting to add every other cell in a 958 row set of numbers.

For instance: =SUM (F5+F7+F9+F11+F13......F957) and then I want to add all
the even number rows, =SUM (F6+F8+F10+F12+F14......F958).

Any suggestions other than typing this formula out.


Post your answer or comment

comments powered by Disqus
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?


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 all,

I am trying to sum 4 consecutive cells that are above 0.

Here is an example:


The sum I want is the first 4 non-0 numbers, which from that example, would be 6.

Thank you all in advanced for your help.

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.

How can I do a countif in non-consecutive cells over a series of rows,
like evens and odds?. I tried this:
but it counts everything on the even rows as well.

Thanks to all.

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 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 a range of data A1:A500 and I'd like to find the sum of every non-consecutive seven cells. For example, I'd like to sum A1:A7 and then sum A8:A14. Can someone please help? Any help would be greatly appreciated. Thanks.

I want to sum 52 non-contiguous cells in column E. The SUM function limits
me to 30. Is there a way around this?

I am trying to sum 44 non contiguous cells. But after I do about 30, it no
longer allows me to select them. Is this something in Excel 2003? A setting I
can change?

Fairly new to Excel. I have learned how to sum the totals of a number of
consecutive cells in a vertical column. I select the cells, stopping the
selection where I want the total to appear ... then select sum from the tool
bar at the top. How to sum (add) when the cells are not consecutive, without
writing out the formula is the question?

Much appreciation



When using DSUM, I would need to have the labels for the criteria and the criteria themselves in non-consecutive rows (e.g. the label would be in A1, there would be a criteria in A2 which I do not want to consider, and the critera I would like to use for the sum is in A3).

Any advice?

Thanks in advance,


My question relates to the thread below:

Could someone tell me how I could construct a single formula which counts non-adjacent cells and has more than one count criterion. I've tried doing this by summing multiple SUMPRODUCTs, but the formula becomes way too long.



I'm normally fairly good with formulae but this one's stumped me and I'd be grateful of any help/ideas. I'm trying to get the worksheet to sum the total of all items in column A for which the value in column B matchs a pre-set value. To give this a practical edge, the spreadsheet contains details of expenses claims by four people: the amount is contained in column A and the name of the claimant in column B. I am trying to get a formula so that will show the total claimed by each person.

A sample spreadsheet showing what I'm trying to achieve is attached - I basically am aiming to get sub totals in the cells indicated.

Many thanks!


I need a macro that would select a range of non-consecutive cells.The problem is that the first cell address is never the same(maybe A21 or A28 or anything). But once the address of the first cell is picked up, all the other non-consecutive cells occur at equal offset distances. May be the user could make the initial selection of the first cell address and a macro could be run to pick-up the rest . How could that be done ?

I wonder if it possible to add non-contiguous cells into a range used by such functions as COUNTIF and LINEST. I tried to do this by using the + sign and CTRL but got an error. I tried:


Maybe the solution in the last case is to use semicolon as the formula argument sequencer. Please let me know.

i recently got a new pc with XP and excel 2007. i seemed to have lost the
ability to select non consecutive cells/rows/columns etc. has anyone else
experienced this?


I have an excel spreadsheet in which I want Excel to identify for every row whether there are more than 2 consecutive cells within a larger range that contain a number that is greater than or equal to 4 and then to calculate the sum of values ONLY in those cells. I also want excel to determine whether that sum for any of such sets within the same row exceeds a certain value. Please see the attachement for example. Thank you for your answer. Grega

I've got a very large spreadsheet and I'm trying to sum a large number of non-consecutive cells.

When I reached a very large sum (more than 30 cells) I get a warning ("The formula you typed contains an error") and excel doesn't make the sum.

Is there an upper limit to the number of non-consecutive cells that you can sum?

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.

When writing a SUM formula, I am unable to incorporate more than 30
non-adjacent cells into my formula. Is there a way around this limit besides
using subtotals?

Hi All,
Please see attached spreadshseet which has 9 highlited non-contiguous variables which need to be 'scored' and then summed at the end of the row (score??).

For each of ac1, ac2, ac3, etc. responses can range from 1 to 4 (although some are missing). I would like to be able to score total row sum using the the following criteria:

if answer=1 or 2, then score = 1
if asnwer=3, then score = -1
if answer=4, then score=0
if answer is missing (blank), then score= MISSING

Using this scoring protocol, scores can range from -9 to +9. As you will see in the attached spreadsheet, however, some persons are missing one or two scores, whereas for others persons all values are missing altogether!

Here is my problem. I want to be able to score each cell (see the 'ifs' above) and then sum across the non-contiguous columns to compute a final score WITHOUT counting the blanks as zeros. In other words, how can I add or SUM items up without Excel putting a darn 'zero' in the rows where there is no data?

Hope I've explained this okay and thanks in advance for your help.


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 want to sum weekly scores where the last 5 are added then the lowest
removed. Some people have a zero value (away that day), so we need to add
the last 5 non-blank cells then deduct the minimum value (that is greater
that zero)

I often go through spreadsheets with hundreds of cells and I need to add maybe 5 of them and create a sum box at the bottom or top, so I'm wondering if theres a way to do this:

Select some (non-consecutive) cells, place an object in them (like a 1 in a circle) in all those cells, then select a place to put in a box that will say "Sum of A's:" and then sum all the selected cells up...

This seems like it might be more trouble than its worth. But any help will be greatly appreciated!

p.s., by box, i just mean some empty cells with a border around them...

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