How do criterion ranges work?
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
Answers
-
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?
-
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.
...
-
@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.
If you'd like any quick support on this, here's a link to schedule a free session.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives