Formula Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
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)
 
Back
Top