???? on sumif?

dawnfoss · Apr 24th, 2002, 11:06 AM

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 · Apr 24th, 2002, 11:17 AM

two ways follow:

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

or

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

Asala42 · Apr 24th, 2002, 11:19 AM

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 · Apr 24th, 2002, 11:19 AM

Try:

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

Asala42 · Apr 24th, 2002, 11:20 AM

case in point,

good call on sumproduct, Dave.

Jay Petrulis · Apr 24th, 2002, 11:22 AM

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 · Apr 24th, 2002, 11:24 AM

Quote:

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

Nice one!

dawnfoss · Apr 24th, 2002, 11:30 AM

tHANKS!!!!!!!

Aladin Akyurek · Apr 24th, 2002, 11:44 AM

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 · Apr 24th, 2002, 11:57 AM

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

dawn

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 24th, 2002, 11:06 AM	#1
dawnfoss Board Regular Join Date: Apr 2002 Posts: 91	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!

Apr 24th, 2002, 11:17 AM	#2
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	two ways follow: =SUMPRODUCT((A1:A3={"Kr","xy"})*(B1:B3)) or =SUMIF(A1:A3,"xy",B1:B3)+SUMIF(A1:A3,"kr",B1:B3)

Apr 24th, 2002, 11:19 AM	#3
Asala42 Board Regular Join Date: Feb 2002 Location: Southfield,MI USA Posts: 1,027	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

Apr 24th, 2002, 11:19 AM	#4
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	Try: =SUMIF(A2:A4,"xy",B2:B4)+SUMIF(A2:A4,"kr",B2:B4)

Apr 24th, 2002, 11:20 AM	#5
Asala42 Board Regular Join Date: Feb 2002 Location: Southfield,MI USA Posts: 1,027	case in point, good call on sumproduct, Dave.

Apr 24th, 2002, 11:22 AM	#6
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	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

Apr 24th, 2002, 11:30 AM	#8
dawnfoss Board Regular Join Date: Apr 2002 Posts: 91	tHANKS!!!!!!!

Apr 24th, 2002, 11:44 AM	#9
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	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

Apr 24th, 2002, 11:57 AM	#10
dawnfoss Board Regular Join Date: Apr 2002 Posts: 91	OH, AND thanks for not pointing out or making fun of the fact that I don't know how to add! dawn