Sumproduct Help

charlesloves2read

Board Regular
Joined
Aug 11, 2010
Messages
55
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Try:

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

HTH
Adam
 
Upvote 0
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)
 
Upvote 0
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?
 
Upvote 0
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)
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

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