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 from cells with multiple values

Bonnie Liu
edited 12/09/19 in Archived 2016 Posts

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".

Tags:

Comments

  • JamesR
    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.

  • 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?

  • 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.

  • Hi Shaine,

     

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

     

    smartsheet.jpg

  • JamesR
    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.

  • 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
    edited 11/03/16

    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! Laughing

This discussion has been closed.