Adding every 3rd cell in a column WITH TEXT

[JackieChrist]

I'm trying to add every third cell in a column, but every second cell contains text. So the column order is as such: #1, #2, TEXT, #1, #2, TEXT, #1, #2, TEXT, etc.
So I want to add all the #1s in one cell, and then all the #2s in another. The only way I know how to add cells like this is by using:

=SUMPRODUCT((O25:O105)*(MOD(ROW(O25:O105)-ROW(O25),3)=0))

But this doesn't work if there's text in the column.

Does anyone know how to get around this? Could you give me a function that would work?

Thanks!

[tkowal]

=IF(MOD(ROW(OFFSET(A1,2,0)),3)=0,"#1",IF(MOD(ROW(OFFSET(A1,1,0)),3)=0,"#2","Text"))

copy down

[Pete_UK]

Try this:

=SUMPRODUCT((ISNUMBER(O25:O105))*(MOD(ROW(O25:O105)-ROW(O25),3)=0),O25:O105)

Hope this helps.

Pete

[JackieChrist]

I'm sorry, I should have clarified that the cells aren't actually filled with "#1" "#2" & "text"...they're filled with various different numbers & actual random text. So I can't specifically write a string of what's in each box. Thanks though.

[JackieChrist]

Pete, that didn't work for me...but I just tried it a second ago..seeing if I can spot some immediate error quickly..

[JackieChrist]

Wait, that did work! Thanks so much Pete!

[Pete_UK]

You're welcome. You need to be careful with all the brackets.

Pete