How do I summarize data by a particular condition and by Date/Year?

Anna WJ

Hello,

I am attempting to create a dashboard that will summarize the cost of overtime for employees working at various locations on a month by month basis to compare cost between either/both conditions (by location and/or by month). In order to do this I have parsed out that I will have to summarize the data in a separate sheet before building my dashboard because the sheet that is being pulled from is not organized (8000 row by 50 column sheet where all employees enter timesheet). I have managed to pull cost of OT by location (as below "a)"), but would like to also add a month, year criterion. I have attempted to pull just the Year/Month using the DATE function (as below "b)"), but results are coming back as $0 (should be approximately $600).

a) =SUMIFS({OT Cost}, {Location}, "Site")

b) =SUMIFS({OT Cost}, {Location}, "Yard", {Date}, DATE(2022, 5))

Any insight would be greatly appreciated.

Paul Newcome

Try something like this for May of 2022.

=SUMIFS({OT Cost}, {Location}, "Site", {Date}, AND(IFERROR(MONTH(@cell), 0) = 5, IFERROR(YEAR(@cell), 0) = 2022))

Paul Newcome

Try something like this for May of 2022.

=SUMIFS({OT Cost}, {Location}, "Site", {Date}, AND(IFERROR(MONTH(@cell), 0) = 5, IFERROR(YEAR(@cell), 0) = 2022))

Anna WJ

Paul Newcome, that worked like a charm!

I was trying all sorts of different referencing of the date column, but everything was coming back "Unparseable" and the equation I noted in my question was the only one coming back with (incorrect data).

Thank you for your help!

Paul Newcome

Happy to help. 👍️