Need some help adding up values in nth cells with text in the cells in between

[newtoexcel86]

i have a problem adding cells every 5th cell for example i have

=SUMPRODUCT((C10:C67)*(MOD(ROW(C10:C67),5)=0))

this adds every 5th row in this range and it does it right as long as there is no text in the cells in between

it works fine when it looks like this

1
1
1
1
1
1
1

but it does not work when it looks like this

1
1
text
1
1
1

when this happens it appears as #VALUE, meaning an error but i don't understand why if i'm just telling it to look at every 5th row and those specific rows have no text in them

i have a spreadsheet with text in between the cells that need to be added so i need a formula that only adds up the cells with numbers on it, to be specific the numbers are either ones or zeroes..... thank you for your help

 

[MandelBrah]

Domenic - That formula doesn't do what was asked. If the range looked like this:

Row 12 - Mom
Row 24 - 6000000
Row 36 - Jun-5-1996

The result would be 2, not 1.

 

[newtoexcel86]

yeah i saw that but that's fine because every 12th row there shouldn't be anything else but dates so as long as that's the case the formula should work fine, right?

 

[Domenic]

that worked like a wonder... i'm trying to learn so if you could tell me what about if i wanted to count cells in the same way but instead of counting the ones with dates, count the ones with a zero or positive numbers... no negative numbers...

For zero's...

=SUMPRODUCT(--(MOD(ROW(C2:C100)-ROW(C2),12)=0),--(C2:C100=0))

If the range can contain empty cells, try the following instead...

=SUMPRODUCT(--(MOD(ROW(C2:C100)-ROW(C2),12)=0),--(C2:C100<>""),--(C2:C100=0))

For positive numbers...

=SUMPRODUCT(--(MOD(ROW(C2:C100)-ROW(C2),12)=0),--(C2:C100>0))

 

[Domenic]

Domenic - That formula doesn't do what was asked. If the range looked like this:

Row 12 - Mom
Row 24 - 6000000
Row 36 - Jun-5-1996

The result would be 2, not 1.

In this case, a helper column can be used to identify the dates. So, for example...

D2, copied down:

=CELL("format",C2)

Then try...

=SUMPRODUCT(--(MOD(ROW(C2:C100)-ROW(C2),12)=0),--ISNUMBER(MATCH(D2:D100,{"D1","D2","D3","D4","D5"},0)))

...which will count dates in any of the following formats...

  d-mmm-yy or dd-mmm-yy
  d-mmm or dd-mmm 
  mmm-yy 
  m/d/yy or m/d/yy h:mm or mm/dd/yy 
  mm/dd

 

[Domenic]

yeah i saw that but that's fine because every 12th row there shouldn't be anything else but dates so as long as that's the case the formula should work fine, right?

Yes, that's right...

 

[newtoexcel86]

none of those are working for me.. i need to count those cells that >=0, so zeros and positive numbers.. i cant seem to get those with those formulas.. it also cannot count blank cells...

 

[MandelBrah]

Didn't get your last post before I did. Disregard.

 

[Domenic]

none of those are working for me.. i need to count those cells that >=0, so zeros and positive numbers.. i cant seem to get those with those formulas.. it also cannot count blank cells...

Misunderstood... Try the following instead...

=SUMPRODUCT(--(MOD(ROW(C2:C100)-ROW(C2),12)=0),--(C2:C100<>""),--(C2:C100>=0))

 

[newtoexcel86]

Thank you... i think that's all the questions i have for now.. i've learned a lot even though i have a lot more to learn..

thank you again