How do criterion ranges work?

Aaron Kennedy

I'm trying to use the SUMIFS formula to count the value of jobs in a certain year worth less than $25,000, but the part of the formula I don't understand is the "criterion_range" part because I'm used to just putting in a range and then a criterion, how does this work?

My current formula (that doesn't work) looks like this:

=SUMIFS({Price}, 1, <25000, {Date}, 1, >=DATE(2024, 1, 1), {Date}, 1, <=DATE(2024,12,31))

The 1's are there as placeholders because I don't know what goes there

kira11

Are you wanting to count the number of jobs that are less than $25,000 and in 2024 or are you wanting to see the dollar value totaled on the jobs that are less than $25,000 and in 2024?

heyjay

criterion_range are usually column name headers.

=SUMIFS({Price},
{Date}, >=DATE(2024, 1, 1),
{Date}, <=DATE(2024,12,31)
)

Line 1 is what column you want to SUM. Succedding lines are your columns, followed by your criteria.

SUMIFs documentation

Matt Lynn-PCG

@Aaron Kennedy when you type in "=sumifs(" it will pop up a helper box that you can expand which will explain each section of the syntax.

The range is just the area of cells that you're pulling values from while the criteria itself is what logic you're wanting to pull from it.

• criterion_range1— The group of cells to be evaluated by the criterion.
• criterion1— The condition that defines which numbers to add; for example: 15, "Hello World!", or >25.
• —[optional] Additional ranges and criteria to have evaluated.
• criterion_range2

Here's an example where the values range is my numerical values I want to sum. But I have 2 criteria ranges. One for names and one for yes/no. So the formula is saying to sum all values IF that row has Aaron and Yes beside it… so it's only the 1st row… sum of 5. If I change the second row to "Yes" then the total would be 15.

https://help.smartsheet.com/function/sumifs

If you'd like any quick support on this, here's a link to schedule a free session.

https://scheduler.zoom.us/t/d1c5ga29/15-min-help-sync