Sumproduct Help

charlesloves2read

Board Regular
Joined
Aug 11, 2010
Messages
53
Hi,

In Column A, I have two customers, A and B.
In Column B, I have 4 factory locations, North, South, East, West.
In Column C, I have Revenue.

How do i use Sumproduct to obtain the Total Revenue for Customer A in Factory Location North AND South.

I only know how to obtain Total Revenue for Customer A in ONE Factory Location.

Thanks!

This is a simplified example of an exercise i am supposed to do at work.
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

AdamL

Well-known Member
Joined
Sep 25, 2008
Messages
767
Try:

=SUMPRODUCT(--(A1:A100="Customer A"),(B1:B100="North")+(B1:B100="South"),C1:C100)

HTH
Adam
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Hi,

In Column A, I have two customers, A and B.
In Column B, I have 4 factory locations, North, South, East, West.
In Column C, I have Revenue.

How do i use Sumproduct to obtain the Total Revenue for Customer A in Factory Location North AND South.

I only know how to obtain Total Revenue for Customer A in ONE Factory Location.

Thanks!

This is a simplified example of an exercise i am supposed to do at work.
One way...

Use cells to hold the criteria:
  • E2 = A
  • F2 = North
  • G2 = South
=SUMPRODUCT(--(A2:A20=E2),--(ISNUMBER(MATCH(B2:B20,F2:G2,0))),C2:C20)
 

charlesloves2read

Board Regular
Joined
Aug 11, 2010
Messages
53

ADVERTISEMENT

One way...

Use cells to hold the criteria:
  • E2 = A
  • F2 = North
  • G2 = South
=SUMPRODUCT(--(A2:A20=E2),--(ISNUMBER(MATCH(B2:B20,F2:G2,0))),C2:C20)

Hi Biff,

I cannot fix the Cells somewhere. Is there any other way i can do it?
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Hi Biff,

I cannot fix the Cells somewhere. Is there any other way i can do it?
You mean you can not use cells to hold the criteria?

In that case one of these:

=SUMPRODUCT(--(A2:A20="A"),--(ISNUMBER(MATCH(B2:B20,{"North","South"},0))),C2:C20)

=SUMPRODUCT(--(A2:A20="A"),--(RIGHT(B2:B20,2)="th"),C2:C20)
 

AdamL

Well-known Member
Joined
Sep 25, 2008
Messages
767

ADVERTISEMENT

What's the -- for?

It's a double unary, in this case a negative of a negative, and it is a way of coercing a boolean value (TRUE or FALSE) into a numerical value (1 or 0). This is one of several ways of doing it (eg multiply by 1, or add 0), but I prefer the -- visually; it makes it clear that next part is a "condition".

I personally think it's a bit strange that SUMPRODUCT doesn't coerce the boolean to a numerical value itself - indeed other spreadsheet applications like Google Spreadsheets you don't need to "manually" coerce the condition - but I guess that's just the way it is.

So a plus in Sumproduct acts as an OR?

Yes, as long as the two conditions are mutually exclusive, as they are in this case (a location can't be both North and South). If they aren't mutually exclusive, you run the risk of double counting, but this is easily worked around; eg if you wanted the total revenue for Customer A or location North:

=SUMPRODUCT(SIGN((A1:A100="Customer A")+(B1:B100="North")),C1:C100)
 

charlesloves2read

Board Regular
Joined
Aug 11, 2010
Messages
53
You mean you can not use cells to hold the criteria?

In that case one of these:

=SUMPRODUCT(--(A2:A20="A"),--(ISNUMBER(MATCH(B2:B20,{"North","South"},0))),C2:C20)

=SUMPRODUCT(--(A2:A20="A"),--(RIGHT(B2:B20,2)="th"),C2:C20)

Is the match syntax wrong? It says lookupvalue first before lookuparray but your is the other way around
 

charlesloves2read

Board Regular
Joined
Aug 11, 2010
Messages
53
It's a double unary, in this case a negative of a negative, and it is a way of coercing a boolean value (TRUE or FALSE) into a numerical value (1 or 0). This is one of several ways of doing it (eg multiply by 1, or add 0), but I prefer the -- visually; it makes it clear that next part is a "condition".

I personally think it's a bit strange that SUMPRODUCT doesn't coerce the boolean to a numerical value itself - indeed other spreadsheet applications like Google Spreadsheets you don't need to "manually" coerce the condition - but I guess that's just the way it is.



Yes, as long as the two conditions are mutually exclusive, as they are in this case (a location can't be both North and South). If they aren't mutually exclusive, you run the risk of double counting, but this is easily worked around; eg if you wanted the total revenue for Customer A or location North:

=SUMPRODUCT(SIGN((A1:A100="Customer A")+(B1:B100="North")),C1:C100)

Hi Adam,

I tried using your formula but it's not working. It only takes up the North values.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,156
Latest member
WDMix

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
Top