sum if and match range of cells in

Options
William Briggs
edited 01/06/23 in Formulas and Functions

Hello, I am trying to sum rows in a column if a text in 1 column matches text in a range in another column. And have that number show up in a Summary field.

If we look at the sheet, I want to sum values in the "# of Views" column if the data in "Workspace Title" match a range of cells in the Workspace column. So the formula would look at row 2 and 30, see that both term sin Workspace Title column match the children under "Professional Skills Development. So therefor return 16 in the Summary field PSD.


I have tried sum , sumifs, sum(collect. But I just can't figure it out. The best I can do is =SUMIF([Workspace Title]:[Workspace Title], HAS(@cell, Workspace2:Workspace30), [# of Views]:[# of Views]). I get a return of 0.


Please help

Tags:

Answers

  • William Briggs
    Options

    I actually figured this out. Not sure how to delete this discussion.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @William Briggs

    If I'm understanding you correctly, you want a formula that SUMs together the values in your Total column if either of the Workspace columns ("Workspace" or "Workspace Title") have a specific matching value, is that correct?

    If so, I would personally write these as separate Summary Fields per-value, like so:

    This would be two SUMIF formulas added together - one for the Workspace column and one for the Workspace Title column, evaluating each cell individually.

    =SUMIF(Workspace:Workspace, "Professional Skills Development", [# of Views]:[# of Views]) + SUMIF([Workspace Title]:[Workspace Title], "Professional Skills Development", [# of Views]:[# of Views])

    This assumes you will not have one row with the same value, otherwise the # of Views will be duplicated. Does that make sense?

    Cheers!

    Genevieve

  • William Briggs
    Options

    Hi @Genevieve P.


    Thank you for the help and explaining it to me so simply. The formula works perfect.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!