SUMIFS formula - if above a certain dollar amount for a specific vendor
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")
Best Answers
-
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:
Good luck!
If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!
-
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)
.
Answers
-
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:
Good luck!
If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!
-
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
-
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)
.
-
I see. That makes perfect sense. Thanks Paul.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!