How can i sum values from one column based on criteria from two corisponding columns

B

Bisto99

New Member
Joined
Aug 30, 2008
Messages
8
I want to calculate sales revenue generated by a particular sales rep based on there "rep code" AND "market sector".

The SUMIF function will work on one criteria but i am struggling to create the above.

E.G: SUM the corresponding cells in column "G"(revenue) IF column "B"(rep code) =57 AND column "E"(market sector) =PD. There will be cell references to the "Rep" and "Market Sector" codes.

I'm sure this would be a simple matter for an expert. I feel im fairly close with what ive tried but can't get it to work. this would save me a lot of time in a new job I've just started. Please Help!!!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
John

I'm sure this can be done with a formula, probably SUMPRODUCT.

Someone will probably be along in a minute with one, not my thing really.

But in the meantime it might be worth looking into Data>Pivot table...
 
Upvote 0
This should work
=SUMPRODUCT(--(B1:B20=57),--(E1:E20="PD"),G1:G20)

Note that SUMPRODUCT only accepts explicit ranges, no full columns (e.g. no B:B)
 
Upvote 0
Thank you, your a genius !!!! It worked first time.

What is the purpose of the -- I've seen these in quotation marks?
 
Upvote 0
When forced to, Excel will perform arithmetic with the logical values TRUE and FALSE. TRUE acts like 1 and FALSE like 0.

The -- converts logicals into numbers, by performing arithmetic:
--(TRUE) = -1 * -1 * TRUE = -1 * -1 * 1 = 1
--(FALSE) = -1*-1 * FALSE = -1 * -1 * 0 = 0
 
Upvote 0
mikerickson said:
When forced to, Excel will perform arithmetic with the logical values TRUE and FALSE. TRUE acts like 1 and FALSE like 0.

The -- converts logicals into numbers, by performing arithmetic:
--(TRUE) = -1 * -1 * TRUE = -1 * -1 * 1 = 1
--(FALSE) = -1*-1 * FALSE = -1 * -1 * 0 = 0
Click to expand...

Multiplication is not what happens, otherwise -- would be too costly...

Harlan Grove, who proposed this "two unary minuses", explains it in 2003 in

http://tinyurl.com/5cwu96

as follows:

'--' is two unary minuses, start with x = 1, then -x = -1, and --x = -(-1) =
1. However, if x = TRUE, -x = -1 (because boolean, TRUE/FALSE, values used
as operands to arithmetic operators are converted to numbers, TRUE to 1,
FALSE to 0), and --x = -(-1) = 1. x+0 and x*1 would accomplish the same
thing (as would N(x)), but --x has the advantage that it binds tighter than
anything else, so there's no chance that you could add anything to the
formula that would screw up the boolean-to-numeric conversion. The reason
this is needed is that if x = TRUE, SUM(x,0) returns 0, but SUM(--x,0)
returns 1.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
SUM of Columns based on multiple criteria with User Form
2
Replies
15
Views
294
Alex Blakenburg
A
S
  • Solved
Sum column based on unique values in another column
2
Replies
11
Views
1K
samide2001
S
D
  • Solved
Count total award value per unique supplier
Replies
4
Views
89
Fluff
Fluff
SanjayGMusafir
  • Solved
Filtering Dates within the dates and adding corresponding values
Replies
4
Views
333
SanjayGMusafir
SanjayGMusafir
E
  • Solved
How to evenly spread out and sum cost items across multiple months based on criteria?
Replies
4
Views
1K
BSALV
BSALV

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

Share this page

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