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

[StephenAF]

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]

Couldn't you accomplish it with a pivot table?

[StephenAF]

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]

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]

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 Avarakkan]

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]

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.

[arshad.99.ali]

Hi Stephen,

I just came up with the another way to solve your thread, i used a simple logic which make easy to get sum

Please have a look attached file

Step1: Sort fields - firstly Presenter then Date - From (Data - Sort)
Step2: Column F- In F3 = IF([@Date]=C2,0,[@['# of people impacted]])
Step4: Do pivot with sum field column F
Or In G Column - We can use Sum if function

Thanks.....