Welcome!

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?

Find more posts tagged with

Comments

ricki

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

Brodiemac

When I do that, it returns #INCORRECT ARGUMENT SET

J. Craig Williams

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

Brodiemac

That did it Craig. Your last formula hit the nail right on the head. Thank you!

J. Craig Williams

That pleases me.

Craig

ryanjpugh

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

J. Craig Williams

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

ryanjpugh

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

J. Craig Williams

Glad I could help.

Craig

ArnaudLegrand

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

Capture d’écran 2017-08-14 à 19.09.05.png

Capture d’écran 2017-08-14 à 19.08.57.png