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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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

S
  • Solved
Sum column based on unique values in another column
2
Replies
11
Views
447
samide2001
S
SanjayGMusafir
  • Solved
Filtering Dates within the dates and adding corresponding values
Replies
4
Views
64
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
T
  • Question
Using VBA, how can I SUM all the values from columns until I find column "Total"? Then do the same from next cell to next "total"
2
Replies
16
Views
1K
Peter_SSs
Peter_SSs
D
  • Question
VBA Code - the code is not pasting to the column I need it to.
Replies
2
Views
584
dragontbone
D

Forum statistics

Threads
1,215,517
Messages
6,125,287
Members
449,218
Latest member
Excel Master

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