sumif with different rows won't work!?
 

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.

sumif with different rows won't work!?
 

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

SUM will skip non number entries



--
Regards
Frank Kabel
Frankfurt, Germany


elmer wrote:
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 B
row 1 100 100
row 2 50 20
row 3 100 100
row 4 25 20

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.

sumif with different rows won't work!?
 

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



--
Regards
Frank Kabel
Frankfurt, Germany


elmer wrote:
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 B
row 1 100 100
row 2 50 20
row 3 100 100
row 4 25 20
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.

sumif with different rows won't work!?
 

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

elmer wrote:

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



--
Regards
Frank Kabel
Frankfurt, Germany


elmer wrote:
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 B
row 1 100 100
row 2 50 20
row 3 100 100
row 4 25 20
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.