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

gene2525

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

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

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

.

Kerry St. Thomas

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!

gene2525

Hi Kerry,

That worked! thanks for you help. However, not sure of the things below, can you help me with these 2 items"

First: If I add a row reference on the "Maintenance Cost" column of your formula "{SUMIFS([Maintenance Cost]1:[Maintenance Cost]781", if get an "#incorrect argument" message; but that range of rows is valid.

Also: Whys is the Maintenance Cost Reference in the formula twice? If I remove one of the "[Maintenance Cost]:[Maintenance Cost]" statements I get the error "invalid operation".

Thank you

Paul Newcome

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

.

gene2525

I see. That makes perfect sense. Thanks Paul.