SUMIF with an OR

[bmartin2]

http://www.mrexcel.com/forum/showthread.php?p=2499135#post2499135

As a follow up to this question yesterday. Same document, same Or statment, but now I want to do this...

A1$20 B1 Registered
A2$70 B2 Walked
A3$100 B3 Ordered
A4$50 B4 Registered
A5$60 B5 Pass
A6$70 B6 Hold

I want my total to summarize the amounts in A1:A6 only when the corresponding B column says registered, ordered or hold.

Thank you all so much. Help is appreciated as always!

 

[Colin Legg]

You can do it in one go with a formula like this:

Excel Workbook
	A	B	C	D	E
1	20	Registered
2	70	Walked		Registered
3	100	Ordered		Ordered
4	50	Registered		Hold
5	60	Pass			240
6	70	Hold
Sheet1

Excel 2003

Cell Formulas
Range	Formula
E5	=SUMPRODUCT(SUMIF(B1:B6,D2:D4,A1:A6))

Or, if you find the individual totals useful then you can break it down like this:

Excel Workbook
	A	B	C	D	E
1	20	Registered
2	70	Walked		Registered	70
3	100	Ordered		Ordered	100
4	50	Registered		Hold	70
5	60	Pass			240
6	70	Hold
Sheet1

Excel 2003

Cell Formulas
Range	Formula
E2	=SUMIF($B$1:$B$6,D2,$A$1:$A$6)
E3	=SUMIF($B$1:$B$6,D3,$A$1:$A$6)
E4	=SUMIF($B$1:$B$6,D4,$A$1:$A$6)
E5	=SUM(E2:E4)

Instead of formulas, you might also find pivot tables useful.

Hope that helps...

 

[ukmikeb]

Or -

=SUMPRODUCT(--(ISNUMBER(MATCH($B$1:$B$6,{"Registered","Ordered","Hold"},0))),$A$1:$A$6)

hth

Mike