SUMPRODUCT(?) if rows meet more than one criteria, Simple Way?

[TEwasko]

Hi all. I am a beginner excel user and new to the forum. I have a very large list of sales reports that I am trying to total up many different ways. Pivot tables work for much of what i'm doing, but I would also just like to try a simple formula for others. Here's an example:

Find total of Red Bikes that Steve had Exchanged, Returned or Traded-In. I know how to use SUMPRODUCT for totalling only one result type from Steve's Red Bike sales, but how do I total two or more conditions. do I just make SUMPRODUCTs for all result types then SUM them together? That seems unessescarily long. Ultimately, I will turn it into a percentage of that total vs all of his sales opportunities and then make nice charts to compare to the other sales reps in the company.

Here's my formula to find just one result type:

=SUMPRODUCT((A2:A21="Steve")*(B2:B21="Red Bike")*(C2:C21="Sold"))
[TABLE="width: 231"]
<TBODY>[TR]
[TD]Rep</SPAN>[/TD]
[TD]Product</SPAN>[/TD]
[TD]Result</SPAN>[/TD]
[/TR]
[TR]
[TD]Steve</SPAN>[/TD]
[TD]Red Bike</SPAN>[/TD]
[TD]Sold</SPAN>[/TD]
[/TR]
[TR]
[TD]Steve</SPAN>[/TD]
[TD]Blue Bike</SPAN>[/TD]
[TD]Sold</SPAN>[/TD]
[/TR]
[TR]
[TD]Steve</SPAN>[/TD]
[TD]Red Bike</SPAN>[/TD]
[TD]Exchanged</SPAN>[/TD]
[/TR]
[TR]
[TD]Steve</SPAN>[/TD]
[TD]Red Bike</SPAN>[/TD]
[TD]Returned</SPAN>[/TD]
[/TR]
[TR]
[TD]Steve</SPAN>[/TD]
[TD]Red Bike</SPAN>[/TD]
[TD]Traded-In</SPAN>[/TD]
[/TR]
[TR]
[TD]Steve</SPAN>[/TD]
[TD]Red Bike</SPAN>[/TD]
[TD]Sold</SPAN>[/TD]
[/TR]
[TR]
[TD]Betty</SPAN>[/TD]
[TD]Red Bike</SPAN>[/TD]
[TD]Exchanged</SPAN>[/TD]
[/TR]
[TR]
[TD]Betty</SPAN>[/TD]
[TD]Blue Bike</SPAN>[/TD]
[TD]Returned</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]

 

[nieve]

Not Tested but something like

=SUMPRODUCT(--(A2:A21="Steve"),--(B2:B21="Red Bike"),--(C2:C21="Sold"))

 

[rallcorn]

Welcome to the board!

Since you say you're new to Excel, I'd suggest setting up the formula to reference the contents of a cell rather than hardcoding "Steve" & "Red Bike", etc.

[TABLE="class: grid, width: 741"]
<TBODY>[TR]
[TD]Rep</SPAN>
[/TD]
[TD]Product</SPAN>
[/TD]
[TD]Result</SPAN>
[/TD]
[TD][/TD]
[TD]Rep</SPAN>
[/TD]
[TD]Product</SPAN>
[/TD]
[TD]Total Trans</SPAN>
[/TD]
[TD]Sold</SPAN>
[/TD]
[TD]Qty Exchg, Retd, Trade</SPAN>
[/TD]
[/TR]
[TR]
[TD]Steve</SPAN>
[/TD]
[TD]Red Bike</SPAN>
[/TD]
[TD]Sold</SPAN>
[/TD]
[TD][/TD]
[TD]Steve</SPAN>
[/TD]
[TD]Red Bike</SPAN>
[/TD]
[TD]5</SPAN>
[/TD]
[TD]2</SPAN>
[/TD]
[TD]3</SPAN>
[/TD]
[/TR]
[TR]
[TD]Steve</SPAN>
[/TD]
[TD]Blue Bike</SPAN>
[/TD]
[TD]Sold</SPAN>
[/TD]
[TD][/TD]
[TD]Betty</SPAN>
[/TD]
[TD]Red Bike</SPAN>
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Steve</SPAN>
[/TD]
[TD]Red Bike</SPAN>
[/TD]
[TD]Exchanged</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve</SPAN>
[/TD]
[TD]Red Bike</SPAN>
[/TD]
[TD]Returned</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve</SPAN>
[/TD]
[TD]Red Bike</SPAN>
[/TD]
[TD]Traded-In</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve</SPAN>
[/TD]
[TD]Red Bike</SPAN>
[/TD]
[TD]Sold</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Betty</SPAN>
[/TD]
[TD]Red Bike</SPAN>
[/TD]
[TD]Exchanged</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Betty</SPAN>
[/TD]
[TD]Blue Bike</SPAN>
[/TD]
[TD]Returned</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

So then the formulas become:
Total Trans G2 =SUMPRODUCT(--($A$2:$A$21=$E2),--($B$2:$B$21=$F2))

Sold H2 =SUMPRODUCT(--($A$2:$A$21=$E2),--($B$2:$B$21=$F2),--($C$2:$C$21=H$1))

Qty Exchg, Retd, Trade I2 =SUMPRODUCT(--($A$2:$A$21=$E2),--($B$2:$B$21=$F2),--($C$2:$C$21<>H$1))

Then copy G2:I2 down to other rows

Anyway, maybe this will get you started in the right direction.

 

[TEwasko]

I like it. A Little bit more simple than what I was working out. And other sheets we have use reference cells and they seem to stand the test of time. Took me a second to realize what your "H1" was doing (saying "give me results that aren't 'sold' "). It works, and fairly easy to alter if I want to omit 'sold' and another value. I guess I gotta learn more about what using -- and <> in formulas does. Thanks!

P.S.
I'm a Photographer who also turns alot of our company's data sheets into meaninful graphic presentations. So sometimes Excel makes me bonkers. I love it and hate it all at the same time.

 

[Robert Mika]

I like it. A Little bit more simple than what I was working out. And other sheets we have use reference cells and they seem to stand the test of time. Took me a second to realize what your "H1" was doing (saying "give me results that aren't 'sold' "). It works, and fairly easy to alter if I want to omit 'sold' and another value. I guess I gotta learn more about what using -- and <> in formulas does. Thanks!

P.S.
I'm a Photographer who also turns alot of our company's data sheets into meaninful graphic presentations. So sometimes Excel makes me bonkers. I love it and hate it all at the same time.

The <> is "not equal to" and the -- is double unary
It is method of coercion, to convert a Boolean value (TRUE or FALSE) to its numeric equivalent (1 or 0)

more on SUMPRODUCT
http://www.xldynamic.com/source/xld.SUMPRODUCT.html#explain

BTW:
Iin Excel 07/10/13 you can use COUNTIFS.

 

[rallcorn]

Happy to help. Yeah, I didn't expect it would provide exactly what you want, but thought it would give you an example & get you going in the right direction once you got the gist of it.

Robert Mika explained the questions you had and correctly pointed out that you can use Countifs if you're using version above Excel 2003.

Countifs formula is easy to understand & construct.
G2 =COUNTIFS($A:$A,$E2,$B:$B,$F2)
H2 =COUNTIFS($A:$A,$E2,$B:$B,$F2,$C:$C,H$1)
I2 =COUNTIFS($A:$A,$E2,$B:$B,$F2,$C:$C,"<>"&H$1)

These can be copied down just like the SUMPRODUCT formulas.