SUMIFS formula - if above a certain dollar amount for a specific vendor

Question

Hi Community,

I checked the archives but didn't see anything related to my question. In the formula below, I am trying to sum the Maintenance Cost of cabinets mad by a vendor named "Boss", if the cost is above $240.  I'm getting the message "unparsable"  So, really just (2) columns to be concerned with ("Vendor" and "Maintenance Cost".  Any help that you can give is appreciated,  Thank you.

=SUMIFS([Maintenance Cost]1:[Maintenance Cost]781, >"$240.00", [Vendor]1:[Vendor781],="Boss")

Kerry St. Thomas · Accepted Answer

I see a few issues:

1. SUMIFS requires you first say what range you're totaling. You're missing a clause at the beginning of your formula.

2. This formula will calculate inaccurately if you have data on rows 782 and higher - so it might not be scalable.

3. The quotes and $ around the 240 makes it impossible to do math with it (because you're converting it into a text value.

This formula will do what you're expecting (but do examine #2 above to make sure it fits your use case).

=SUMIFS([Maintenance Cost]:[Maintenance Cost], [Maintenance Cost]:[Maintenance Cost], >240, Vendor:Vendor, "Boss")

Read more about SUMIFS here:

https://help.smartsheet.com/function/sumifs

Good luck!

Paul Newcome · Accepted Answer

If you are referencing the entire column, just use the column names. Do not specify row numbers. If you do want to specify row numbers, all ranges must be the same number of rows.

It has to be in there twice. The first is the "range to sum" and the second is to set it as a criteria range since you want to evaluate for more than 240.

=SUMIFS(range to sum, 1st criteria range, 1st criteria, 2nd criteria range, 2nd criteria)

https://help.smartsheet.com/function/sumifs

.