Formula Question

[Guest]

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

 

[Guest]

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

 

[T. Valko]

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

 

[Don Guillett]

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","Mark"})*salesrng)