sumif not working if using different rows
 

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

--
Regards,
Tom Ogilvy

"elmer" wrote in message
...
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 not working if using different rows
 

ahhh. Thanks, we got it at the same time, that is what I finally came up with, only it took you 1/10th of the time.
Thanks much.

----- Tom Ogilvy wrote: ----

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

--
Regards
Tom Ogilv

"elmer" wrote in messag
..
Perhaps you misunderstood my issue. Using your formula works good if ro
2 and 4 has data in them. If B4 is blank, your formula says the answer i
..2375. However the answer should be .3166. Since row 2 has data in bot
cells then sum of row 1 is 200, but since row 4 only has data in one cel
(A4), sum of row 3 is only 100. Therefore 95/300 = .316
Thanks for your help
----- Frank Kabel wrote: ----
H
in this case simply us
=sum(a2:b2,a4:b4)/sum(a1:b1,a3:b3
SUM will skip non number entrie
-
Regard
Frank Kabe
Frankfurt, German
elmer wrote
Trying to do a sumif but not all in the same row. I want to sum ro

and 4 and divide it by sum of row 1 and 3, but only if cells in ro

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