SUMIF Function in Excel 5.0
Gord Thompson
> A B C
> Buy Grey 80
> Buy Blue 80
> Buy Grey 90
> Sell Grey 100
>I would like the SUMIF to work if A="Buy" and B="Grey", giving me the answer
>170. I have wriiten the function like:
> SUMIF(Sheet!A:B, AND(Sheet!A="Buy",Sheet!B="Grey",Sheet!C:C)
>But this gives me an answer of zero, which is not what I want. Does anybody
>have any suggestions on how to do this with SUMIF or is there some other way of
>accomplishing this? Thank you.
How about an array function:
A B C
1 Buy Grey 80
2 Buy Blue 80
3 Buy Grey 90
4 Sell Grey 100
5
6 Buy Grey 170 <-- {=SUM(IF(B1:B4=$B$6,1,0)*IF(C1:C4=$C$6,1,0)*D1:D4)}
Vance Wilber
KIR...@QUCDN.QueensU.CA writes:
>I have been trying to use the SUMIF function to do some calculations.
But I
>would like the summing to occur if there is a certain value in Column A
and
>another certain value in column B. For example:
> A B C
> 1 Buy Grey 80
> 2 Buy Blue 80
> 3 Buy Grey 90
> 4 Sell Grey 100
answer
>170. I have wriiten the function like:
> SUMIF(Sheet!A:B, AND(Sheet!A="Buy",Sheet!B="Grey",Sheet!C:C)
>But this gives me an answer of zero, which is not what I want. Does
anybody
>have any suggestions on how to do this with SUMIF or is there some other
way of
>accomplishing this? Thank you.
>
I played around with the SUMIF function a little a couple of weeks ago.
As far as I could tell, you CAN'T do an AND statement within the SUMIF
function.
Although it's not as pretty as an AND within the SUMIF statement, you
could of course add a column D where D1 reads
=IF(and(A1="Buy",B1="Grey"),C1,"").
I ended up having to do something closer to my suggestion above, which
eliminates the SUMIF function altogether. If you find a way to use an
AND statement within the SUMIF please post it here.
Good luck,
Vance
John Bilicska
>I played around with the SUMIF function a little a couple of weeks ago.
>As far as I could tell, you CAN'T do an AND statement within the SUMIF
>function.
Yikes! You're absolutely right, of course. I'll double check before I
post stuff from now on. Mea culpa.
--
John Bilicska "All this science I don't understand,
jbil...@orion.oac.uci.edu it's just my job five days a week"
Dept. of Chemistry, UC-Irvine --Elton John _Rocket_Man_
[Also: bili...@well.sf.ca.us]
Erich Neuwirth
: I have been trying to use the SUMIF function to do some calculations. But I
: would like the summing to occur if there is a certain value in Column A and
: another certain value in column B. For example:
: A B C
: Buy Blue 80
: Buy Grey 90
: I would like the SUMIF to work if A="Buy" and B="Grey", giving me the answer
: 170. I have wriiten the function like:
: SUMIF(Sheet!A:B, AND(Sheet!A="Buy",Sheet!B="Grey",Sheet!C:C)
: But this gives me an answer of zero, which is not what I want. Does anybody
: have any suggestions on how to do this with SUMIF or is there some other way of
: accomplishing this? Thank you.
=sumproduct(if(a1:Ax="Buy",1,0),if(b1:bx="Grey",1,0),c1:c10)
entered as an arry function (with ctrl-hift-enter)
should do the trick.
i think that array functions are one of the best features of excel,
and BY FAR TOO FEW people know about them.