How do I add multiple values that match multiple conditions?
 

I have been trying to do this forever. I am using this for sales metrics,
and reporting. Here is the data
Date Rep Revenue Service
01-24-2005 Joel $20 $5
01-24-2005 Bob $22 $7
01-24-2005 Joel $14 $3
01-25-2005 Joel $27 $10
01-25-2005 Joel $22 $11

Ok what I want to do is sum up how much Joel sold on 01-24-2005 ($34), So
the conditions are Joel, and the date. How would you add that up without
adding Bob's numbers in there?

How do I add multiple values that match multiple conditions?
 

=SUMPRODUCT(--(A2:A20=--"2005-01024"),--(B2:B20="Joel"))

You could also put the values in cells and test against those.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Joel" wrote in message
...
I have been trying to do this forever. I am using this for sales metrics,
and reporting. Here is the data
Date Rep Revenue Service
01-24-2005 Joel $20 $5
01-24-2005 Bob $22 $7
01-24-2005 Joel $14 $3
01-25-2005 Joel $27 $10
01-25-2005 Joel $22 $11

Ok what I want to do is sum up how much Joel sold on 01-24-2005 ($34), So
the conditions are Joel, and the date. How would you add that up without
adding Bob's numbers in there?

How do I add multiple values that match multiple conditions?
 

Bob,
A typo plus missing data but for me another lesson on comparing
dates (--"2005-01-24"):

=SUMPRODUCT(--(A2:A20=--"2005-01-24"),--(B2:B20="Joel"),--(C2:C20))


"Bob Phillips" wrote:

=SUMPRODUCT(--(A2:A20=--"2005-01024"),--(B2:B20="Joel"))

You could also put the values in cells and test against those.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Joel" wrote in message
...
I have been trying to do this forever. I am using this for sales metrics,
and reporting. Here is the data
Date Rep Revenue Service
01-24-2005 Joel $20 $5
01-24-2005 Bob $22 $7
01-24-2005 Joel $14 $3
01-25-2005 Joel $27 $10
01-25-2005 Joel $22 $11

Ok what I want to do is sum up how much Joel sold on 01-24-2005 ($34), So
the conditions are Joel, and the date. How would you add that up without
adding Bob's numbers in there?

How do I add multiple values that match multiple conditions?
 

Thanks Toppers, I must be locked into counting today.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Toppers" wrote in message
...
Bob,
A typo plus missing data but for me another lesson on comparing
dates (--"2005-01-24"):

=SUMPRODUCT(--(A2:A20=--"2005-01-24"),--(B2:B20="Joel"),--(C2:C20))


"Bob Phillips" wrote:

=SUMPRODUCT(--(A2:A20=--"2005-01024"),--(B2:B20="Joel"))

You could also put the values in cells and test against those.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Joel" wrote in message
...
I have been trying to do this forever. I am using this for sales
metrics,
and reporting. Here is the data
Date Rep Revenue Service
01-24-2005 Joel $20 $5
01-24-2005 Bob $22 $7
01-24-2005 Joel $14 $3
01-25-2005 Joel $27 $10
01-25-2005 Joel $22 $11

Ok what I want to do is sum up how much Joel sold on 01-24-2005 ($34),
So
the conditions are Joel, and the date. How would you add that up
without
adding Bob's numbers in there?

How do I add multiple values that match multiple conditions?
 

Whilst the OP's question was related to how much Joel sold on the 24th,
If Bob's formula were changed to

=SUMPRODUCT(--(A$2:A$20=A2),--(B$2:B$20=B2),--(C$2:C$20))

put on row 2, and formula-copied down each row, it would show the
date/rep total for the date/rep mentioned on that line, but obviously
Joel's sales on the 24th, and on the 25th, would both be repeated on
multiple lines (two lines in the test data shown).

I see no way to restrict this to show on the first occurance only, but
this may help the OP in the sales analysis.

--

Bob Phillips Wrote:
Thanks Toppers, I must be locked into counting today.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Toppers" wrote in message
...
Bob,
A typo plus missing data but for me another lesson on
comparing
dates (--"2005-01-24"):

=SUMPRODUCT(--(A2:A20=--"2005-01-24"),--(B2:B20="Joel"),--(C2:C20))


"Bob Phillips" wrote:

=SUMPRODUCT(--(A2:A20=--"2005-01024"),--(B2:B20="Joel"))

You could also put the values in cells and test against those.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Joel" wrote in message
...
I have been trying to do this forever. I am using this for
sales
metrics,
and reporting. Here is the data
Date Rep Revenue Service
01-24-2005 Joel $20 $5
01-24-2005 Bob $22 $7
01-24-2005 Joel $14 $3
01-25-2005 Joel $27 $10
01-25-2005 Joel $22 $11

Ok what I want to do is sum up how much Joel sold on 01-24-2005
($34),
So
the conditions are Joel, and the date. How would you add that
up
without
adding Bob's numbers in there?





--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=531456

How do I add multiple values that match multiple conditions?
 

"Bryan Hessey"
wrote in message
news:Bryan.Hessey.261sna_1144671601.7293@excelforu m-nospam.com...

I see no way to restrict this to show on the first occurance only, but
this may help the OP in the sales analysis.

=IF(SUMPRODUCT(--(A$2:A2=A2),--(B$2:B2=B2))=1,SUMPRODUCT(--(A$2:A$20=A2),--(
B$2:B$20=B2),C$2:C$20),"")

Note also that the data being aggregated in a SP doesn't neeed to be
coereced with --.