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?

=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?

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?

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?

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

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