Help with formula

I am looking to create a formula that will add the number value in one column based on criteria from another column.

Using the example below, I'd like to add the number value in the "No. of Images" column based on the criteria in the "Image Type" column. For example, the Logo w/key would add a value of 6, and any further entries of Logo w/key would continue to add the associated value to the running total. I tried doing a SUMIF but couldn't figure out how to establish the criteria from another column


Best Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hi Ryan,


    Try this:

    =SUMIF([image type]:[image type],CONTAINS("Logo w/key",@cell),[No. of images]:[No. of images])


    Hope this helps!

    Best,

    Heather

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Ah! That does help. In this case, I end up back at the helper column thing. Since you're using the Metric column for items other than contacts, you can't turn it into a contact column.

    I'd suggest you create a Contact column in between Metric and Totals. Set it as a contact list. You can still leave everyone's names in the Metric column. Once you've set everyone's names in both columns, you can hide the Contact column.

    In the Totals column you would then use

    =SUMIF([Designer Assigned]:[Designer Assigned], [Contact]@row, [No. of Images]:[No. of Images])


    See if that works for you. Let me know!


    Thanks,

    Heather

«1

Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hi Ryan,


    Try this:

    =SUMIF([image type]:[image type],CONTAINS("Logo w/key",@cell),[No. of images]:[No. of images])


    Hope this helps!

    Best,

    Heather

  • That did the trick! Thank you so much!

  • @Heather D one more question...

    If I want to use the same formula, but the criteria is from a Contact List column, how do i represent that in the formula? I tried both typing the name as well as the email address and had no success.

    Thoughts?

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    Hi Ryan,


    I had difficulty with this, too. I did find a way to do it, though. Where are you putting this information - on a separate sheet? Smartsheet summary on the same sheet? Rows at the top of the same sheet? That will help me guide your answer.


    Best,

    Heather

  • I am putting this formula into a cell on the same sheet off to the side. It's a Text/Number column. I was trying

    =SUMIF([Designer Assigned]:[Designer Assigned], CONTAINS("Christopher Ironson", @cell), [No. of Images]:[No. of Images])

    But it didnt work. It's giving me a Sum of 0 (zero), when it should be 24

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    Ok. I would suggest you add a helper column (I called mine Summary - Designer) next to the summary cell you're entering the formula in. Make it a contact column. Then add a row for each contact you want to summarize, assigning a different contact to each row of the summary rows.

    Enter this formula into the summary column:

    =SUMIF([Designer Assigned]:[Designer Assigned], [Summary - Designer]@row,[No. of Images]:[No. of Images])


    This translates to - sum all of the numbers of images where the Designer Assigned matches the Designer whose name is in the Summary - Designer column on this row.


    Does that make sense?


    Thanks,

    Heather

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    Here's a screenshot if it helps:


  • @Heather D


    Hi there! That certainly gives me some progress. However, I need to have this happen on any row where the Designer is listed in both the helper column and the contact list. Looks like this formula only calculates the one row. Ultimately I want a running total of Images that any given Designer is assigned, based on the vaule in the "No. of Images" column


  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    Ryan,

    Oh! That makes it somewhat easier then. Forget the helper column (my apologies). If you're wanting a column that just gives a running total of how many images are assigned to each designer in every row, you can just make a total column and use this:

    =SUMIF([Designer Assigned]:[Designer Assigned], [Designer Assigned]@row,[No. of Images]:[No. of Images])

    This will add up all of the numbers of images for the designer in that row. Let me know if this works/is what you're looking for.


    Best,

    Heather

  • Hi Heather, Sorry for my confusion! This wont work, as my need is to add up all values that are assigned to a given Designer on multiple rows. So in my example I gave, Chris Ironson's total should be 24 because he has values of 8, 4, 5 and 7 in the No. of Images column where he is listed in the Designer Assigned column, which happens to be a Contact List. When i run the formula you gave me, i get 8, which is the value of that first row he is assigned to

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    Ryan,

    That is strange! It should give a total of all images assigned to him in the sheet.

    =SUMIF([Designer Assigned]:[Designer Assigned], [Designer Assigned]@row, [No. of Images]:[No. of Images])

    The above formula is working for me. Does it do the same for other contacts?


    Thanks,

    Heather

  • Doe the location of the cell where I am putting this formula matter? I have columns far off to the right, separate from the grid where I am gather various metrics.


  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Ah! That does help. In this case, I end up back at the helper column thing. Since you're using the Metric column for items other than contacts, you can't turn it into a contact column.

    I'd suggest you create a Contact column in between Metric and Totals. Set it as a contact list. You can still leave everyone's names in the Metric column. Once you've set everyone's names in both columns, you can hide the Contact column.

    In the Totals column you would then use

    =SUMIF([Designer Assigned]:[Designer Assigned], [Contact]@row, [No. of Images]:[No. of Images])


    See if that works for you. Let me know!


    Thanks,

    Heather

  • YESSS!!! That works! Thank you so much Heather. This is a monster help for me and my team. I really appreciate your time helping me today! Best wishes!

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    Happy to help! Glad we finally got it figured out.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!