Need to SUM units based on multiple criteria while ignoring duplicate dates

**StephenAF** · 12-13-2011, 03:56 PM

I have a worksheet that I need help on (See attached).

I need a formula that can track the total # of people impacted (Column 'E') per presenter (Column 'A') , but I need the formula to be able to account for lessons that occured on the same day (Column 'C').

Example:

A ............B...............C..........D.....E
Arun....Lesson 5.....14-Nov...3.75...18
Arun....Lesson 8.....14-Nov...3.75...18
Arun....Lesson 11...14-Nov... 3.75...18
Arun....Lesson 9.....10-Oct....3.75...30
Arun....Lesson 10...31-Oct....3.75...31
Sam....Lesson 5.....14-Nov...3.75...18
Sam....Lesson 8.....14-Nov...3.75...18
Ben.....Lesson 11...14-Nov... 3.75...18
Ben.....Lesson 9.....10-Oct....3.75...30
Ben.....Lesson 10...31-Oct....3.75...31

For "Arun", a basic SUM function would return 115, but I need a formula that would return a value of 79, because Lessons 5 8 & 11 occured on the same day and only impacted 18 people (i.e., not 54)

**Miraun** · 12-13-2011, 04:34 PM

Couldn't you accomplish it with a pivot table?

**StephenAF** · 12-13-2011, 05:09 PM

I've tried that, but the way I'm already using the pivot table just totals the numberws per presenter as a whole. So the numbers are much hire than they really are. I need this formula so that I can have the correct info in the source data for the pivot table.

**dilipandey** · 12-14-2011, 11:50 PM

Hi StephenAF,

See the attached file and let me know if this works for you

Regards,
DILIPandey

<click on below 'star' if this helps>

**StephenAF** · 12-15-2011, 03:06 PM

Hi DILIPandey,

The results were exactly what I needed, but I need them to show in one cell that is apart of the source code. This dummy sheet represents a piece of a much larger sheet that acts as the source code for another pivot table.

The ideal state is a formula that can calculate the #'s to the total on your sheet, but not in a pivot table format.

Cheers,
StephenAF

**Haseeb A** · 12-15-2011, 05:04 PM

This formula will give the Arun's Total # of people impacted (EXCLUDING duplicate dates)

=SUM(IF(SIGN(FREQUENCY(IF(Table4[Presenter]="Arun",Table4[Date]),Table4[Date])),Table4['# of people impacted]))

Confirmed with CTRL+SHIFT+ENTER

**StephenAF** · 12-16-2011, 11:37 AM

Haseeb,

That's amazing! Thank you. That works perfectly. Surprisingly I'm already using a code VERY similar to this in this workbook, but I couldn't seem to adjust it for this purpose.

Thank you very much everyone for your help.