SUMIF by Vendor and Date

cnikkih

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?

Genevieve P.

Hi @cnikkih,

Yes, you are correct! If your column is a Date type then we’ll need to add in a specific date function to extract the year, instead of using “FIND”. 

Try this:

=SUMIFS(Amount:Amount, Vendor:Vendor, ="Alphabet", InvoiceDate:InvoiceDate, YEAR(@cell) = 2019)

You are also correct in saying that SUMIF (singular) requires the column to be summed at the end, but since you are looking for two criteria (the Vendor and the year) you will need to use a SUMIFS (with an S).

SUMIFS are structured in the opposite way: first with the column to sum, then all of the different criteria. Here are the Help Center articles I referenced to build this formula:

• SUMIFS
• YEAR
• @ cell

Let me know if this works for you!

Cheers,

Genevieve

Genevieve P.

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.

Cheers,

Genevieve

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!

Genevieve P.

Hi @cnikkih,

Yes, you are correct! If your column is a Date type then we’ll need to add in a specific date function to extract the year, instead of using “FIND”. 

Try this:

=SUMIFS(Amount:Amount, Vendor:Vendor, ="Alphabet", InvoiceDate:InvoiceDate, YEAR(@cell) = 2019)

You are also correct in saying that SUMIF (singular) requires the column to be summed at the end, but since you are looking for two criteria (the Vendor and the year) you will need to use a SUMIFS (with an S).

SUMIFS are structured in the opposite way: first with the column to sum, then all of the different criteria. Here are the Help Center articles I referenced to build this formula:

• SUMIFS
• YEAR
• @ cell

Let me know if this works for you!

Cheers,

Genevieve