SUMIF by Vendor and Date

I am new to Smartsheets and cannot figure out the formula for a particular SUMIF. Basically, I need the total by Vendor & Year. I have an incredibly long list of invoices that I've imported into Smartsheets, and want to be able to use a dashboard off of the Sheet Summary to report on the totals by Vendor for each of the last 3 years.

For example, using the chart below, how would I show that we spent $2430.00 with Alphabet in 2019, leaving out the entries from 2018 and 2020?


Best Answer


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @cnikkih,

    Since your date column does not look to be a date type of column (see here about date columns), then we could combine a SUMIFS formula with a FIND function to find the year. 

    We would use SUMIFS (with an S) because there are multiple criteria. Try something like this:

    =SUMIFS(Amount:Amount, Vendor:Vendor, ="Alphabet", Date:Date, FIND("2019", @cell) > 0)

    With a SUMIFS, you first list the column you want to SUM, then each Criteria Range and the Criteria after it. (Click here for more information on SUMIFS). 

    Let me know if this works for you! If not, it would be useful to see a screen capture of your sheet in Grid view with your sheet summary field open to the formula.



  • cnikkih
    cnikkih ✭✭✭✭

    Thank you for the help, but that returned an #UNPARSEABLE error. Possibly because the my Date field IS a date type? I just went and verified that. The chart I added earlier was simply to give an idea of what I was looking for, it is not a 1 to 1 representation of our sheet.

    I have added a screenshot for your reference. I previously used another community post to write the original SUMIF statements, and that post recommended putting the column to be summed at the end of the statement (Amount:Amount) as shown in the second field on the sheet summary. They are working as expected.

    Also in the sheet summary shown, I used your suggestion for the first line but changed Date to InvoiceDate.

    Thanks again!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!