sumifs formula with various criteria

B Young

I was wanting some help with a sumifs formula where I'm trying to sum a qty if another column meets certain criteria and then the date column is between 2 dates. My current formula is:

=SUMIFS({QTY}, {Team Member}, "Team Member 1", {Date Absent}, [Start of Month]@row >= [Start of Month]@row, {Date Absent}, [Last Day of Month]@row <= [Last Day of Month]@row)

When {Qty} contains a number, {Team Member} contains a team member name and {Date Absent} contains a date - all referencing another sheet. The [Start of Month] and [Last Day of Month] are date columns on the sheet containing the formula.

I am trying to find the sum of amounts in {Qty} when it says "Team Member 1" in {Team Member} and the date in {Date Absent} is between the date in [Start of Month] and the date in [Last Day of Month}.

What do I have wrong in the formula?

B Young

Thank you - this works perfectly! Really appreciate your help on this one!

Paul Newcome

Try this:

=SUMIFS({QTY}, {Team Member}, "Team Member 1", {Date Absent}, @cell >= [Start of Month]@row, {Date Absent}, @cell <= [Last Day of Month]@row)

B Young

Thank you! Why would this be returning a zero value when it should be 1? See attached sheets. The notification sheet is where the Tally Sheet is pulling the data from.

Tally Sheet.png

Notification Sheet.png

Paul Newcome

Exactly how is the Qty column being populated?

B Young

It has a formula in it =IF([Time of Day]@row = "", "", IF([Time of Day]@row = "All Day", "1", "0.5"))

Time of Day column has the option for All Day or Part Day - Qty will have nothing in it if Time of Day is blank and if it says All Day it will have 1 or Part Day 0.5.

Would this be causing the problem?

Paul Newcome

Yes. When you put "quotes" around a number, it outputs a text string that just looks like a number. If you remove the quotes, it should output a numerical value which can then be summed.

B Young

Thank you - this works perfectly! Really appreciate your help on this one!

Paul Newcome

Happy to help. 👍️