???? on sumif?

[dawnfoss]

a b

1 ab 20
2 xy 10
3 kr 15

I want to add the values of b ONly if the values in a = xy or kr. The result should be 35.

Help!

 

[Dave Patton]

two ways follow:

=SUMPRODUCT((A1:A3={"Kr","xy"})*(B1:B3))

or

=SUMIF(A1:A3,"xy",B1:B3)+SUMIF(A1:A3,"kr",B1:B3)

 

[Asala42]

Is this ok?

=sumif($A$1:$A$100,"xy",$B$1:$B$100)+sumif($A$1:$A$100,"kr",$B$1:$B$100)

Hope that helps, -someone may post a shorter formula

Adam

 

[Aladin Akyurek]

Try:

=SUMIF(A2:A4,"xy",B2:B4)+SUMIF(A2:A4,"kr",B2:B4)

 

[Asala42]

case in point,

good call on sumproduct, Dave.

 

[Jay Petrulis]

Hi,

Try,

=SUMIF(A1:A3,"ab",B1:B3)+SUMIF(A1:A3,"kr",B1:B3)

or

=SUMPRODUCT(((A1:A3="ab")+(A1:A3="kr"))*(B1:B3))

Bye,
Jay

 

[Jay Petrulis]

On 2002-04-24 10:17, Dave Patton wrote:
=SUMPRODUCT((A1:A3={"Kr","xy"})*(B1:B3))

Nice one!

 

[dawnfoss]

tHANKS!!!!!!!

 

[Aladin Akyurek]

I'd suggest using the SUMIF version, which allows you to use the cell refs instead of "kr", etc. That is, you can put e.g., "xr" in E1 and "kr" in E2 and use:

=SUMIF(A2:A4,E1,B2:B4)+SUMIF(A2:A4,E2,B2:B4)

If the OR criteria/conditions count 3 or more, you might want to consider using:

=SUMPRODUCT((ISNUMBER(MATCH(A2:A100,E1:E3,0)))*(B2:B100))

where E1:E3 houses the OR criteria.

Aladin

 

[dawnfoss]

OH, AND thanks for not pointing out or making fun of the fact that I don't know how to add!

dawn