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

TEwasko

New Member
Joined
Jan 18, 2013
Messages
2
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"))
Rep</SPAN>Product</SPAN>Result</SPAN>
Steve</SPAN>Red Bike</SPAN>Sold</SPAN>
Steve</SPAN>Blue Bike</SPAN>Sold</SPAN>
Steve</SPAN>Red Bike</SPAN>Exchanged</SPAN>
Steve</SPAN>Red Bike</SPAN>Returned</SPAN>
Steve</SPAN>Red Bike</SPAN>Traded-In</SPAN>
Steve</SPAN>Red Bike</SPAN>Sold</SPAN>
Betty</SPAN>Red Bike</SPAN>Exchanged</SPAN>
Betty</SPAN>Blue Bike</SPAN>Returned</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Not Tested but something like

Code:
=SUMPRODUCT(--(A2:A21="Steve"),--(B2:B21="Red Bike"),--(C2:C21="Sold"))
 
Upvote 0
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.

Rep</SPAN>
Product</SPAN>
Result</SPAN>
Rep</SPAN>
Product</SPAN>
Total Trans</SPAN>
Sold</SPAN>
Qty Exchg, Retd, Trade</SPAN>
Steve</SPAN>
Red Bike</SPAN>
Sold</SPAN>
Steve</SPAN>
Red Bike</SPAN>
5</SPAN>
2</SPAN>
3</SPAN>
Steve</SPAN>
Blue Bike</SPAN>
Sold</SPAN>
Betty</SPAN>
Red Bike</SPAN>
1
1
Steve</SPAN>
Red Bike</SPAN>
Exchanged</SPAN>
Steve</SPAN>
Red Bike</SPAN>
Returned</SPAN>
Steve</SPAN>
Red Bike</SPAN>
Traded-In</SPAN>
Steve</SPAN>
Red Bike</SPAN>
Sold</SPAN>
Betty</SPAN>
Red Bike</SPAN>
Exchanged</SPAN>
Betty</SPAN>
Blue Bike</SPAN>
Returned</SPAN>

<TBODY>
</TBODY>

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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top