Counting every nth occurrence (with & without a condition)

Hi,

I have a few dozen columns of data and would like from each row to count the number of negative numbers in every 3 occurrences. E.g. in row 2 I’d want to count the negative occurences in B2, E2, H2, K2 etc.

Also, I’d like to be able to count the total number of occurences along each row, again counting every 3rd occurrence, though this time both negative and positive values.

Can someone please suggest a formula for each of these two problems?

Thanks!

Hi,

Try these:

=SUMPRODUCT(((MOD(COLUMN(A2:AA2),3))=2)*(A2:AA2<0))
=SUMPRODUCT(((MOD(COLUMN(A2:AA2),3))=2)*(NOT(A2:AA2="")))

To count the negative values:

Please Login or Register  to view this content.

To count all numeric values (assuming there may be text in some cells):

Please Login or Register  to view this content.

Does that help?

if your starting and including b2 then you need to adjust so col b=3 so add 1 to the formula as shown
=SUMPRODUCT(((MOD(COLUMN(B2:K2)+1,3)=0)*(B2:K2<0)))
or i suppose just as the others suggested leave it at
MOD(COLUMN(B2:AG2),3)=2)*......
just my preference really