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

[Bisto99]

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!!!

 

[Norie]

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

 

[mikerickson]

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)

 

[Bisto99]

Thank you, your a genius !!!! It worked first time.

What is the purpose of the -- I've seen these in quotation marks?

 

[mikerickson]

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

 

[Aladin Akyurek]

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

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.