Sum ifs 3 criteria

Karrie Davis
Karrie Davis Overachievers Alumni
edited 12/09/19 in Formulas and Functions

I'm looking for the sumifs= Status column is "green", The Trans Date column in January 2018 the reversed column is unchecked.

 

So far I only have the first part of the formula working. I cannot figure out how to include the rest

=SUMIF(@{Collections Range 3}, @cell = "Green", {Collections Range 1}) This information will be referenced in another sheet.

if formula.PNG

Comments

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    Are you summing the "amount" column based on the 3 criteria? If so, this format should work just update the column references:

    =SUMIFS(Amount:Amount, Status:Status, ="Green", Reversed:Reversed, =0, [Tran Date]:[Tran Date], IFERROR(MONTH(@cell), 0) = 1)

     

  • Karrie Davis
    Karrie Davis Overachievers Alumni

    The formula worked, I added another criteria however it calculated everything not just January

     

    =SUMIFS({Collections Range 1}, {Collections Range 2}, ="Receipt", {Collections Range 3}, ="Green", {Collections Range 4}, =0, {Collections Range 5}, IFERROR(MONTH(@cell), 0) = 1)

  • Karrie Davis
    Karrie Davis Overachievers Alumni

    Actually it does give me January just not 2018, I have multiple years on a the original chart how can I specifiy to just one year

     

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    Here is mine with the option of a specific year. 

    =SUMIFS(Amount:Amount, Status:Status, ="Green", Reversed:Reversed, =0, [Tran Date]:[Tran Date], IFERROR(MONTH(@cell), 0) = 1, [Tran Date]:[Tran Date], IFERROR(YEAR(@cell), 0) = 2019)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You can also use an OR or an AND statement if you are referencing the same range twice. I personally find it helps me keep track of what I have and have not accounted for yet, keeps things a little more organized, and helps keep the overall length of the formula down a touch.

     

    =SUMIFS(Amount:Amount, Status:Status, ="Green", Reversed:Reversed, =0, [Tran Date]:[Tran Date], AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2019))

    .

    You can also use cell references for the month and year criteria to save yourself from having to go in and change the formula with each month/year combo. I see you are using a different sheet for your calculations, so you could add a helper column that has separate cells for the month and year entry (for this example I'll call it [Date Helper]).

    =SUMIFS(Amount:Amount, Status:Status, ="Green", Reversed:Reversed, =0, [Tran Date]:[Tran Date], AND(IFERROR(MONTH(@cell), 0) = [Date Helper]1, IFERROR(YEAR(@cell), 0) = [Date Helper]2))

    .

    You can also use cell references to auto-update when dragfilling if you are tracking multiple month/year combos in a grid type layout.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!