SUMIFS and HAS between a date range

HR_Training

I'm attempting a formula for finding the total number of hours when a cell contains a specific name in a given month. I've been able to make the formula work without the date range with the following:

=SUMIF({Custom Request Tracker 2024 Facilitators}, HAS(@cell, [Requested Hours by Facilitator]@row), {Custom Request Tracker 2024 Session Length})

I attempted to add a date range to the end of the formula and received an incorrect argument error. How do I fix this formula?

=SUMIF({Custom Request Tracker 2024 Facilitators}, HAS(@cell, [Requested Hours by Facilitator]@row), {Custom Request Tracker 2024 Session Length}, {Custom Request Tracker 2024 Confirmed Date}, @cell >= DATE(2024, 1, 1), {Custom Request Tracker 2024 Confirmed Date}, @cell <= DATE(2024, 1, 31)

KPH

Your SUMIF can only include one piece of logic.

To have multiple criteria you need to use SUMIFS.

The syntax is a little different between SUMIF and SUMIFS. In SUMIF the thing to sum is optional and goes last, in SUMIFS it is required and goes first.

So, bearing in mind I don't know what these sheet references refer to, I think something like this would work:

=SUMIFS({Custom Request Tracker 2024 Session Length},{Custom Request Tracker 2024 Facilitators}, HAS(@cell, [Requested Hours by Facilitator]@row), {Custom Request Tracker 2024 Confirmed Date}, @cell >= DATE(2024, 1, 1), {Custom Request Tracker 2024 Confirmed Date}, @cell <= DATE(2024, 1, 31))

Let me know how you get on!

HR_Training

Thank you! I finally got the following formula to work:

=SUMIFS({Custom Request Tracker 2024 Session Length}, {Custom Request Tracker 2024 Facilitator}, CONTAINS([Requested Facilitator]@row, @cell), {Custom Request Tracker 2024 Confirmed Date}, MONTH(@cell) = 1)

Now, I'm having a similar problem. I'm attempting to count the number of times "Strategic Planning" is listed as the requested service if a specific facilitator is selected (there is the potential for multiple facilitators to be listed) if the confirmed date is in January. The following formula is working if there is only 1 facilitator listed:

=COUNTIFS({Custom Request Tracker 2024 Requested Service}, [Requests]@row, {Custom Request Tracker 2024 Facilitator}, CONTAINS("Facilitator", @cell), {Custom Request Tracker 2024 Confirmed Date}, MONTH(@cell) = 1)

If there are multiple facilitators listed, I'm getting an Invalid Data Type error. How do I adjust this formula to account for the potential of multiple facilitators listed, but I only want it counted for 1 specific facilitator?

KPH

Hi @HR_Training

Try using HAS again rather than CONTAINS. HAS is specifically designed for multi-select columns. It checks if a cell has a particular selection (alongside others).

HR_Training

The HAS is returning the same information, so that does not seem to be the issue. However, I'm still getting an INVALID DATA TYPE error on the same rows as before. The more I investigated, it wasn't just returning the error on multiple facilitators. It's working on almost all the rows, but not all. Even weirder, it's not even consistent on the rows it's not working.

Here is a screenshot of the invalid data type errors, both where it is working and where it is not working (1st two columns are referencing Debbie, second two are referencing Allen, last 2 are referencing Kaitlin):

Here is the formula:

Here is the source page with the relevant columns, filtered to show Allen's results. Where the error is showing, he should be receiving a result of 0 in both rows. He does have those services scheduled, however not in January.

KPH

Can you try putting an IFERROR around the formula to return 0, if there is an error?

=IFERROR(COUNTIFS({Custom Request Tracker 2024 Requested Service}, [Requests]@row, {Custom Request Tracker 2024 Facilitator}, CONTAINS("Facilitator", @cell), {Custom Request Tracker 2024 Confirmed Date}, MONTH(@cell) = 1),0)

HR_Training

Thank you so much! I think that would have worked. I ended up changing the date criteria, which seemed to work. Here is what I finally was able to do:

=COUNTIFS({Custom Request Tracker 2024 Requested Service}, [Requests]@row, {Custom Request Tracker 2024 Facilitator}, CONTAINS("Facilitator", @cell), {Custom Request Tracker 2024 Confirmed Date}, >=DATE(2023, 1, 1), {Custom Request Tracker 2024 Confirmed Date}, <=DATE(2024, 1, 31))

Thank you so much for your help! I appreciate the input!

KPH

Wonderful, happy to help!