Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
SUMIF not working properly
I am trying to create some summary totals on an inventory sheet. We stock several model Laserjet printers. Each row has it's own sum. Now I'm trying to create an overall sum of Laserjets by using the following formula:
=SUMIF([Inventory Item]1:[Inventory Item]86, "Laserjet", [In Stock]1:[In Stock]86)
If I interpret how SUMIF works in SmartSheet, this formula looks in the Inventory Item cells 1-86 for the word Laserjet and returns the sum of the associated cells in the In Stock cells 1-86. However, it constantly returns the value of 0. What am I doing wrong? Is there some kind of wilcard I need to do with the work Laserjet to make this work?
Comments
-
I think you need
=SUMIF([Inventory Item]1:[Inventory Item]86, "Laserjet", [In Stock]:[In Stock]
Leave off the numbers in the field you want the sum of
-
When I do that, it returns #INCORRECT ARGUMENT SET
-
Brodiemac,
ricki's formula is malformed. The range size in the first and third argument need to be the same.
The names of your columns lead me to other possible errors.
[in Stock] sounds like a check box column.
Is it?
Or is the number of items in stock?
(Both make sense, I just want to be clear. I will assume [In Stock] is a number of items.
[Inventory Item] appears to be an item name.
Are the names "Laserjet" only or do they contain other information like "Laserjet HP4100"
If only Laserjet and a set of numbers then this should work:
=SUMIF([Inventory Item]1:[Inventory Item]86, "Laserjet", [In Stock]1:[In Stock]86)
for rows 1 to 86
and this should work for all rows
=SUMIF([Inventory Item]:[Inventory Item], "Laserjet", [In Stock]:[In Stock])
But it appears you tried that.
So, I'll assume that "Laserjet" is only part of the [Inventory Item] name.
If so, try this
=SUMIF([Inventory Item]1:[Inventory Item]86, FIND("Laserjet", @cell) > 0, [In Stock]1:[In Stock]86)
Hope that helps.
Craig
-
That did it Craig. Your last formula hit the nail right on the head. Thank you!
-
That pleases me.
Craig
-
Craig,
I am having the same exact issue. I have a "Status" column with dropdown options of "Pending" and "Accepted". I have a second column with a dollar amount in each cell.
I want to sum the amount of all rows marked "Pending" and then do the same thing with all rows marked "Accepted".
I have tried: =SUMIF([Status]1:[Status]64, "Pending", [CAR Amount]1:[CAR Amount]64)
as well as
=SUMIF([Status:]1:[Status:]64, FIND("Pending", @cell) > 0, [CAR Amount]1:[CAR Amount]64)
with no luck. I keep getting $0 returned. Any help? Thanks.
Ryan
-
ryan
I assume you have limited the range to rows 1-64 on purpose and that is not the issue.
The formulas look OK.
The only thing I can think of is that somehow the data in the [CAR Amount] is Text and that the column is also formatted for percentage.
The SUMIF formula is returning 0 which is formatted to use USD currency.
Check if there is a formula used to get the value for [CAR Amount]. It should NOT have a $ in the result or the result is being converted to Text.
If that is not the case, you can share the sheet with me (craig.williams@ronin-global.com) and I can take look.
Craig
-
Thanks, Craig. I knew it would be something simple - the CAR Amount column was set as a Date column. Changing it to Text/Number immediately made the formula work.
Ryan
-
Glad I could help.
Craig
-
Hello Craig
i experiment the same issue
sorry for my english but i m french
i copy your formula below and try to adapt with "laserjet" type
i want to make a sum if a name appear in the row i write it but doesn t work
=SUMIF( [Syndic]1: [Syndic]1132]; FIND("Crouzet"; @cell) > 1; [Total HT]1:[Total HT]1132)
in order to find "Crouzet" wich is a part of name ex "Crouzet Breil"
I m new to Smartsheet et don t understand why
Appriciate help of community
after i will reuse the same formula to make some with different type of name exemple "foncia", "taboni" but can appeat in row "cabinet Foncia" , or "cabinet Taboni"
regards
return error is #unparseable
legrand@renovcanalisation.com
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives