Looking for solution (Function) based on Condition IF and SUMIF

[Lauris K]

Hello all,

I'm working on the utilization report and as a part of that report there is additional requirement to understand if our Agents are logging the time on particular date that they are completing work or different day (after). this has a significan impact sometimes and needs to be tracked down to understand the impact and to manage it.

the requirement is quite basic -
1. Agent works today and logs in hours today
2. logging hours today is a requirement (not tomorrow or any other day)
3. In our world it does not happen

this is why I need to find a solution (a formula) or maybe a suggestion of a different technical approach (?)

The task is in the table, in Agent logs hours today for yesterday, the cell of table should return how many hour were logged today for yesterday.

Please see a quick snapshot below and Table with mock data attached.
Capture.JPG

Test WorkBook:
TEST workbook.xlsx

Any help would be highly appreciated.

Thanks,
L.

[63falcondude]

Try this in B3:

=SUMIFS($D$17:$D$43,$A$17:$A$43,$A3,$B$17:$B$43,B$2,$C$17:$C$43,"<>"&B$2)

Drag to the right and down.

Note that you have a typo in C2.

If you want blanks instead of 0's, you can use this:

=IFERROR(1/(1/SUMIFS($D$17:$D$43,$A$17:$A$43,$A3,$B$17:$B$43,B$2,$C$17:$C$43,"<>"&B$2)),"")

[Lauris K]

Works, and thank you for outlining the typo

[63falcondude]

Happy to help. Thanks for the rep!