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

 

[Domenic]

Try...

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

 

[newtoexcel86]

thank you for that.. now if i wanted to do it so it reads all of column c starting at row 23 what would i have to do?

 

[Domenic]

Try...

=SUMPRODUCT(--(MOD(ROW(C23:C65536)-ROW(C23),5)=0),C23:C65536)

However, if the data does not actually extent to Row 65536, use a dynamic named range to refer to the data. A search on this Board for 'dynamic named ranges' should yield some examples. Actually, if you're using Excel 2003, you can convert the data in to a list (Data > List > Create List). The range will automatically adjust as data is added/removed.

 

[newtoexcel86]

ok ill do that.. thank you for your help but i have one more question.. i have the same problem that i had before when i had text in between but now in some of the cells i have #value meaning an error in that cell.. but i'm not telling it to read that cell so it shouldn't.. last question i have
thank you in advance

 

[Domenic]

Presumably, the error is the result of a formula. If this is the case, how about modifying the formula so that it returns a formula blank/null text string ("") instead of #VALUE! ? This way, SUMPRODUCT will ignore the formula blanks. Otherwise...

=SUM(IF(ISNUMBER(C23:C65536),IF(MOD(ROW(C23:C65536)-ROW(C23),5)=0,C23:C65536)))

...which needs to be confirmed with CONTROL+SHIFT+ENTER.

 

[newtoexcel86]

ok that's getting a little complicated for me but thx anyways

 

[newtoexcel86]

sorry to bother so much but i need a formula that would be like countif,
that would count every 12th cell in a column only if those cells contain a date. with text in the cells in between same as before but the countif formula that would only count the amount of cells with dates in them

 

[Domenic]

Try...

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

Adjust the ranges, accordingly.

 

[newtoexcel86]

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...