# SumIfs based on Multiple criteria

Options
✭✭✭✭

Trying to sum up an amount based on 2 criteria's. i'm getting \$0.0

=SUMIFS({Retainer/Deposit Forcasting Range 1}, {Retainer/Deposit Forcasting Range 3}, AND(IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = Week@row, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2023), {Retain/Dep Range 3}, "Deposit", {Retain/Dep Range 3}, "Retainer")

• ✭✭✭✭
Options

This formula worked

=SUMIFS({Retainer/Deposit Forcasting Range 1}, {Retainer/Deposit Forcasting Range 3}, AND(IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = Week@row, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2023), {Retain/Dep Range 3}, "Deposit") + SUMIFS({Retainer/Deposit Forcasting Range 1}, {Retainer/Deposit Forcasting Range 3}, AND(IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = Week@row, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2023), {Retain/Dep Range 3}, "Retainer")

• ✭✭✭✭
Options

I figured it out. No need to reply:)

• ✭✭✭✭✭✭
Options

Can you post an example of the data? (what each range has)

Formula seems good...

• ✭✭✭✭
Options

This formula worked

=SUMIFS({Retainer/Deposit Forcasting Range 1}, {Retainer/Deposit Forcasting Range 3}, AND(IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = Week@row, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2023), {Retain/Dep Range 3}, "Deposit") + SUMIFS({Retainer/Deposit Forcasting Range 1}, {Retainer/Deposit Forcasting Range 3}, AND(IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = Week@row, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2023), {Retain/Dep Range 3}, "Retainer")

• ✭✭✭✭✭✭
Options

You can simplify using the OR function on your Range 3

=SUMIFS({Retainer/Deposit Forcasting Range 1}, {Retainer/Deposit Forcasting Range 3}, AND(IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = Week@row, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2023), {Retain/Dep Range 3},OR(@cell= "Deposit", @cell="Retainer"))

• ✭✭✭✭
Options

@Leibel S Thank you that works in a new sheet that I created.

How would I pull from an additional sheet but only want to include "un checked" Hide retained job column?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!