Sumifs Distinct Date

[Nikijune]

Hello all

I have a database in excel containing total hours worked by date.

On the odd occasion the user will enter there hours worked more than once, if they submit their user form multiple times through out the day.

I am trying to build a report that summarises the total number of hours worked for that agent and for that month, but I only want to sum the hours for each day once, ignoring the multiple entries.

I have the basis of what I want below, but I cant seem to find anything to help me sum the below if the date appears more than once.

=SUMIFS("Sum Hours worked", "Name field", "Agents Name", "Date Field", "Date"

Can anyone help please?

 

[steve the fish]

You can do a sumifs/countifs to do individual dates then sum them up to get a full month eg:

=SUMIFS(A2:A6,B2:B6,B2,C2:C6,C2)/COUNTIFS(B2:B6,B2,C2:C6,C2)

I cant think of a way to get a whole month from data held in individual dates in one formula though i dont doubt it could be done.

 

[Nikijune]

Thanks Steve the Fish

I will need to sum the hours for each day of the month so I'm not sure that would work...I mean it would work, but I'd have to count if for each individual date.

I came across something that said that was a SUMPRODUCT(FREQUENCY) which counted the number of times a unique value appeared but I couldn't get it to work for dates

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($B$2:$B$6=E2,MATCH($C$2:$C$6,$C$2:$C$6,0)),ROW($C$2:$C$6)-ROW(INDEX($C$2:$C$6,1,1))+1),$A$2:$A$6))

where B = agent names, C = dates, A = hours worked, and E2 houses an agent name of interest.


Hope this helps.