Need formula to calculate sum with 2 columns as criterias

Options

Hello all,

I have a situation where I need to count only specific values from a particular column, with one criteria coming from a different column. For example I have Projects in the first column and a number under 2021 column. I need a formula to look at the Projects column, to find only let's say Project 1 and then refer to the 2021 column and get all values that belong to that project from the 2021 column. I won't be able to sort all projects alphabetically that's the reason I need this kind of formula.

Any help is greatly appreciated!

Answers

  • Chris Mondeau
    Chris Mondeau ✭✭✭✭✭✭
    Options

    Hi @Jona Gjylameti

    I took a stab at what I think your sheet might look like:


    The Project column has a name (or number), then under the 2021 column is just the names of projects that occurred. Next to that is an Amount Column (could a budget, or hours, or headcount)

    All I do is add a column called total; this sums all values from the Amount column where the row in the 2021 matches the Project value.

    So for Project (highlighted in the formula), we're adding all Dollar Amounts that have a matching "2021" name using the @cell reference.

    Hopefully this helps, if not please share a screenshot of your sheet and we can take another try.

  • Jona Gjylameti
    Options

    Hi Chris,

    Thanks for the response. What I'm trying to do is more like the example below:

    On my total column, I want the total of all cells that belong to Project 1 and since Project 1 is scattered throughout the sheet, I need that formula to search on the entire column for project 1 and calculate the total sum from the 2021 rates. Do you think that's that possible?



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!