SUMIF from cells with multiple values

Bonnie Liu

I'm looking to pull a budget report that will help calculate the total of a cell if the criterion cell has multiple values. I have a formula that works in Excel, but doesn't seem to translate into Smartsheet. I think my issue is how to specify the correct value for the criterion.

 

Does anyone know the best way to convert my excel formula to Smartsheet?

 

=(SUMIF(K22:K45, "*Nashville*",L22:L45))

 

In the "Nashville" cell, i have it listed out with multiple city names (i.e. "San Francisco, Nashville, Charlotte"). I want to be able to have the cell calculate the adjacent number if the criterion contains "Nashville".

JamesR

Bonnie,

 

=sumif(City22:City45,"Nashville",Value22:Value45)

 

This assumes the column containing the cities is called [City] and the Column containing the values to be added is called [Value].

 

You appear to have an "*" in your excel formulae that might be an issue.

Bonnie Liu

James,

 

Thanks for your quick response. It seems your formula doesn't work either. I wondered if it was the "*", but it doesn't seem to make a difference. Also, for some reason, the only way for the formula to capture the "Cost Per Event" column is when it's listed as "[Cost Per Event]14:[Cost Per Event]21". I'm guessing this is still correct because of the spaces in the title of the column. The result of my trials have shown either a $0 balance or #UNPARSEABLE.

 

This is what it looks like now with a $0 balance

=SUMIF(Notes14:Notes21, "Kansas", [Cost Per Event]14:[Cost Per Event]21)

 

Wondering if I'm missing something?

Shaine Greenwood

Hello Bonnie! From what I'm seeing in the formula example you provided, you have a combination of rich and plain text characters—this can interfere with Smartsheet's ability to interpret the formula.

 

If you are copying and pasting formulas from any other source (including from the Community) into Smartsheet, then I'd recommend deleting the formula and manually typing the formula into the cell in Smartsheet.

 

Let us know how that works out.

Bonnie Liu

Hi Shaine,

 

I tried typing it in manually a couple times, it doesn't seem to work either. Here's a screenshot for reference.

 

JamesR

Bonnie,

 

I do not think that you can use wild cards in Formulae.

 

I will try and find a way to achieve this but it will most likly involve at least one extra column,  When I have sorted it I will let you know.

Shaine Greenwood

Hi Bonnie,

 

James is correct, there isn't a way to use wildcards in your SUMIF criteria, so the formula probably won't find anything to sum (returning 0) even if it doesn't present an error.

 

The closest I can think of you getting to this, would be to use the LEFT function in your formula, combined with @cell. I made an example of this below:

 

=SUMIF(Notes14:Notes21, LEFT(@cell, 6) = "Kansas", [Cost Per Event]14:[Cost Per Event]21)

 

This formula will sum the range of "Cost Per Event" if the first six characters of your "Notes" column range are equal to the "Kansas" text string.

 

Caveats to this:

1. This is a case-sensitive, character sensitive value, meaning the formula will pass over "kansas" or any different uppercase/lowercase combination of the word. It'll only sum for "Kansas" with a capital K. The word "Kansas" must be spelled correctly as well.

 

2. This will only sum cells that contain "Kansas" first and foremost in the cell. The LEFT function is looking for characters in the cell starting at the first character position. If, for example, you have "Hello, Kansas" or even ".Kansas" in the cell, it won't sum the adjacent value.

 

—I'll get your vote down for wildcard options in our formulas on our enhancement request list for further review by our Product team!

 

Bonnie Liu

Shaine,

 

Adding the LEFT function formula worked!! It's so interesting how there's that additional piece that I couldn't think to use. 

 

Thank you both Shaine and JamesR on helping me through this so quickly. I really appreciate your help!