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

  • kira11
    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
    heyjay ✭✭✭✭✭
    edited 08/01/24

    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
    Matt Lynn-PCG ✭✭✭✭✭✭
    edited 08/01/24

    @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

    Certified Platinum Partner

    2023 Partner of the Year

    PrimeConsulting.com