How do I add multiple values that match multiple conditions?

Joel · 04-10-2006, 05:15 AM

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 Phillips · 04-10-2006, 06:00 AM

=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" <[email protected]> wrote in message
news:[email protected]...
> 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?

Toppers · 04-10-2006, 07:10 AM

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 Phillips · 04-10-2006, 07:15 AM

Thanks Toppers, I must be locked into counting today.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Toppers" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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** · 04-10-2006, 08:17 AM

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 row, but obviously Joel's sales on the 24th, and on the 25th, would both be repeated on multiple rows (two rows 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.

--

Originally Posted by Bob Phillips

Thanks Toppers, I must be locked into counting today.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Toppers" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 Phillips · 04-10-2006, 08:35 AM

"Bryan Hessey" <[email protected]>
wrote in message
news:[email protected]...

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