sum function (every nth row)

[asdvender]

Thanks rylo for your help last time...

This time I have a different question about the summation function.
Basically I need Excel to automatically sum values of a particular cell in a row. The rows are going to be evenly spaced so like sum every 5th row of column B.
Or if Ax=1 then add B1 to a running summation...

I have attached an example excel file to show what I was trying to do...

Thanks!

[daddylonglegs]

You could use a formula that would sum every 10th row, i.e.

=SUMPRODUCT(--(MOD(ROW(A1:A100)-ROW(A1),10)=0),B1:B100)

but it would be better, and simpler, to utilise the 1s, i.e.

=SUMIF(A:A,1,B:B)

Note: that given your example these two give different results because you don't have a 1 in A31

[asdvender]

but it would be better, and simpler, to utilise the 1s, i.e.

=SUMIF(A:A,1,B:B)

Wow, that was obvious! I never used that function before. made it way easier! Thanks daddylonglegs!

Quick question on the same example though...
Is it possible now to find the number of those critical values above a certain value? Like after I know the sum now, I want to know how many of the critical values are above 30. I was trying to utilize the =SUMPRODUCT(--()) function, but I can't figure it out.

[Domenic]

Try...

=SUMPRODUCT(--(MOD(ROW(A1:A100)-ROW(A1),10)=0),--(B1:B100>30))

Or, utilizing the same 1's...

=SUMPRODUCT(--(A1:A100=1),--(B1:B100>30))

Hope this helps!

[asdvender]

Or, utilizing the same 1's...

=SUMPRODUCT(--(A1:A100=1),--(B1:B100>30))

Hope this helps!

Thanks Domenic! That worked like a charm, I thought I could do it with sumproduct, but couldn't figure it out. The more I do with this the more I want to figure out.

One LAST question on this example...
Is there a way to find the Maximum of the Critical values?
=SUMIF(A:A,1,B:B) found the sum of them...
There is no MAXIF function, but something like
MAXIF(A:A,1,B:B)...or...IF(A:A=1,MAX(B:B),0)
Those are bad examples, but I think they convey what I am struggling to do.

[deadlyduck]

Or even this variation(!):

Please Login or Register  to view this content.

[daddylonglegs]

You can't use whole columns but you can use a formula like

=MAX(IF(A1:A100=1,B1:B100))

This is an "array formula" which needs to be confirmed with CTRL+SHIFT+ENTER. When you do that correctly curly brackets will appear around the formula in the formula bar

[asdvender]

Sorry for the delay. That worked fine. Thanks!