sumif with different rows won't work!?

E

elmer

Trying to do a sumif but not all in the same row. I want to sum row 2 and 4 and divide it by sum of row 1 and 3, but only if cells in row 2 and 4 have numbers in them.

A
row 1 100 10
row 2 50 2
row 3 100 10
row 4 25 2

So, =sum(a2:b2,a4:b4)/sumif(a2:b2,a4:b4,">0",a1:b1,a3:b3

But it won't do because of the , between b1 and a3.
Help! Thank You.
 
F

Frank Kabel

Hi
in this case simply use
=sum(a2:b2,a4:b4)/sum(a1:b1,a3:b3)

SUM will skip non number entries
 
E

elmer

Perhaps you misunderstood my issue. Using your formula works good if row 2 and 4 has data in them. If B4 is blank, your formula says the answer is .2375. However the answer should be .3166. Since row 2 has data in both cells then sum of row 1 is 200, but since row 4 only has data in one cell (A4), sum of row 3 is only 100. Therefore 95/300 = .3166

Thanks for your help.

----- Frank Kabel wrote: -----

Hi
in this case simply use
=sum(a2:b2,a4:b4)/sum(a1:b1,a3:b3)

SUM will skip non number entries
 
J

Jerry W. Lewis

Perhaps your explanation isn't clear. I think you are asking for

=SUM(A2:B2,A4:B4)/SUM(ISNUMBER(A2:B2)*A1:B1,ISNUMBER(A4:B4)*A3:B3)

array entered (Ctrl-Shift-Enter)

Jerry
 
Top