Formula Question
 

Hello All -

I am stumped and I know you can help me!

Background information:
I am working on a spreadsheet for all of our sales reps that need to sum all
of the sales allocated to them for the each month.

The workbook contains 2 worksheets: Summary and Detail tab

The formula works if there is only one sales rep. However, when we have a
sales rep leaving and one replacing them and they both had a sale in the same
month, this is where the formula needs to sum both of them for the month.

Here is the current formula that I have created that works with only one
sales rep:
=SUMPRODUCT(Detail!$E$1:$E$998="Pat McGurk")
For the month of February, it states 4 for the four sales allocated to Pat
McGurk.

i.e. Pat McGurk is the sales rep who is leaving and had 4 sales allocated
to him in February. Mark Young is the sales rep who is replacing Pat and had
one sale allocated to him in February. I want to the formula to sum both Pat
& Mark's sales to show a total of 5 sales for the month of February.

How do I create a formula that will look for Pat McGurk and Mark Young and
will sum both of their sales for the month to total 5?

I appreciate any help you can provide!!

Fitzi

Formula Question
 

Just a thought...

It sounds like this doesn't happen too often. Assumming you know what the
answer should be, why not just enter "5" manually?

Cheers,

Glen

"Fitzi" wrote:

Hello All -

I am stumped and I know you can help me!

Background information:
I am working on a spreadsheet for all of our sales reps that need to sum all
of the sales allocated to them for the each month.

The workbook contains 2 worksheets: Summary and Detail tab

The formula works if there is only one sales rep. However, when we have a
sales rep leaving and one replacing them and they both had a sale in the same
month, this is where the formula needs to sum both of them for the month.

Here is the current formula that I have created that works with only one
sales rep:
=SUMPRODUCT(Detail!$E$1:$E$998="Pat McGurk")
For the month of February, it states 4 for the four sales allocated to Pat
McGurk.

i.e. Pat McGurk is the sales rep who is leaving and had 4 sales allocated
to him in February. Mark Young is the sales rep who is replacing Pat and had
one sale allocated to him in February. I want to the formula to sum both Pat
& Mark's sales to show a total of 5 sales for the month of February.

How do I create a formula that will look for Pat McGurk and Mark Young and
will sum both of their sales for the month to total 5?

I appreciate any help you can provide!!

Fitzi

Formula Question
 

Try one of these:

=COUNTIF(Detail!$E$1:$E$998,"Pat McGurk")+COUNTIF(Detail!$E$1:$E$998,"Mark
Young")

=SUMPRODUCT(--(Detail!$E$1:$E$998={"Pat McGurk","Mark Young"}))

Although the first formula is a few keystrokes longer than the other
formula, it's better.

Biff

"Fitzi" wrote in message
...
Hello All -

I am stumped and I know you can help me!

Background information:
I am working on a spreadsheet for all of our sales reps that need to sum
all
of the sales allocated to them for the each month.

The workbook contains 2 worksheets: Summary and Detail tab

The formula works if there is only one sales rep. However, when we have a
sales rep leaving and one replacing them and they both had a sale in the
same
month, this is where the formula needs to sum both of them for the month.

Here is the current formula that I have created that works with only one
sales rep:
=SUMPRODUCT(Detail!$E$1:$E$998="Pat McGurk")
For the month of February, it states 4 for the four sales allocated to Pat
McGurk.

i.e. Pat McGurk is the sales rep who is leaving and had 4 sales allocated
to him in February. Mark Young is the sales rep who is replacing Pat and
had
one sale allocated to him in February. I want to the formula to sum both
Pat
& Mark's sales to show a total of 5 sales for the month of February.

How do I create a formula that will look for Pat McGurk and Mark Young and
will sum both of their sales for the month to total 5?

I appreciate any help you can provide!!

Fitzi

Formula Question
 

Use this idea if daterng is properly formatted dates. Use full name. You may
want to use defined names for the ranges as I have so they are
SELF-adjusting for additions/deletions.

=sumproduct((month(daterng)=1)*(namerng={"Pat","Ma rk"})*salesrng)

--
Don Guillett
SalesAid Software

"Fitzi" wrote in message
...
Hello All -

I am stumped and I know you can help me!

Background information:
I am working on a spreadsheet for all of our sales reps that need to sum
all
of the sales allocated to them for the each month.

The workbook contains 2 worksheets: Summary and Detail tab

The formula works if there is only one sales rep. However, when we have a
sales rep leaving and one replacing them and they both had a sale in the
same
month, this is where the formula needs to sum both of them for the month.

Here is the current formula that I have created that works with only one
sales rep:
=SUMPRODUCT(Detail!$E$1:$E$998="Pat McGurk")
For the month of February, it states 4 for the four sales allocated to Pat
McGurk.

i.e. Pat McGurk is the sales rep who is leaving and had 4 sales allocated
to him in February. Mark Young is the sales rep who is replacing Pat and
had
one sale allocated to him in February. I want to the formula to sum both
Pat
& Mark's sales to show a total of 5 sales for the month of February.

How do I create a formula that will look for Pat McGurk and Mark Young and
will sum both of their sales for the month to total 5?

I appreciate any help you can provide!!

Fitzi