sumif multiple criteria

[basher450]

little help with something I thought would be simple:
in A column I have names:
A1 = "josh"
A2 = "dani"
A3 = "bobby"

in B column I have numbers:
B1 = 2
B2 = 4
B3 = 5

I want to sum the B values for "josh" and "dani" only. (B1 and B2)

I tried this:
=SUMIF(A1:A3, "josh, dani", B1:B3)
and this:
=SUMIF(A1:A3, OR(josh, dani), B1:B3)

always returns 0

 

[Matty]

How about:

Excel Workbook
	A	B	C	D	E	F
1	josh	2		josh		6
2	dani	4		dani
3	bobby	5
Sheet1

Excel 2003

Cell Formulas
Range	Formula
F1	=SUMPRODUCT(SUMIF(A1:A3,D1:D2,B1:B3))

D1:D2 contains the values you want to sum from the dataset A1:B3.

Does this help?

Matty

 

[wsjackman]

Sum if will not take multiple criteria
If you have 2007 sumifs will.

meanwhile try

=SUMIF(A1:A3, "josh", B1:B3)+=SUMIF(A1:A3, "dani", B1:B3)

 

[barry houdini]

If you want a single SUMIF as you attempted then the syntax would be like this....

=SUM(SUMIF(A1:A3, {"josh", "dani"}, B1:B3))

Edit: of course that's similar to Matty's suggestion. If you use the "array constant" {"josh", "dani"} then you can use SUM but if you replace that with a range then you can use SUMPRODUCT to avoid CSE.......

 

[Matty]

Hello barry houdini,

Is CSE actually required with your array constant suggestion? It seems to work without...

Matty

 

[Aladin Akyurek]

Hello barry houdini,

Is CSE actually required with your array constant suggestion? It seems to work without...

Matty

{"josh", "dani"} as criteria, which is an array constant, you need an ordinary SUM to total the SumIf results, no CSE.

If you use a range like D1:D2 housing the criteria, you need array processing:

Either...

=SUMPRODUCT(SUMIF(A1:A3,D1:D2,B1:B3))

which needs just enter;

Or...

=SUM(SUMIF(A1:A3,D1:D2,B1:B3))

which would require CSE...

 

[Colin Legg]

Hi Matty,

With the inline array constant, no CSE is not required. But if you use a named array constant or a range containing the list, then CSE is required (or, as in your example, SUMPRODUCT() instead of SUM()).

We discussed this in some depth here:
http://www.mrexcel.com/forum/showthread.php?t=367772

 

[basher450]

How about:

Excel Workbook
	A	B	C	D	E	F
1	josh	2		josh		6
2	dani	4		dani
3	bobby	5
Sheet1

Excel 2003

Cell Formulas
Range	Formula
F1	=SUMPRODUCT(SUMIF(A1:A3,D1:D2,B1:B3))

D1:D2 contains the values you want to sum from the dataset A1:B3.

Does this help?

Matty

this worked
thankd

 

[Matty]

Thanks to Aladin and Colin for their explanations.

The link Colin posted is a real eye-opener. Shall bear the results in mind when doing conditional summing in future!

Cheers,

Matty