# sumifs formula with various criteria

Options
✭✭

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?

## Best Answer

• ✭✭
Answer ✓
Options

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

## Answers

• ✭✭✭✭✭✭
Options

Try this:

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

• ✭✭
Options

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.

• ✭✭✭✭✭✭
Options

Exactly how is the Qty column being populated?

• ✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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.

• ✭✭
Answer ✓
Options

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

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!