Sumif Formula

Stefanie Kneissl

Hello everyone,

could you please help me to find a solution for the following problem:

In order to compare our Annual Operation costs, we display our expected(=phased) costs for each month accross all projects.

We work here with a sumif formula: =SUMIF([planned/phased/spent]:[planned/phased/spent]; "phased = actual + expected spend"; Jan:Jan)

now we want to do the same for planned costs. Unfortunately I get an error for both formulas

#blocked in the cell i want to input the new formula and #circular reference in the cell which we summed the phased costs already before.

If i type in the formula for all Feb values in March, the the sum if formula works :/

Does anyone has an idea what I'm doing wrong here?

Thank you

James Keuning

I think you need to reconsider your data structure. But first, the way your formula is written, Smartsheet is looking at the planned/phased/spent column and looking for the value in the column: "phased = actual + expected spend"

Like it is looking for that literal string of text.

If you really want to evaluate if the phased field equals the sum of actual and expected spend, create a checkbox column with the formula phased = (actual + expected spend) which will result in a T/F answer that you can use as your criterion.

The bigger problem I see is that you are using column names to store data. This is going to result in a data layout that is "short and fat," and you should consider going long and skinny. If I am understanding your data correctly, you want five columns: Project Name, Month (YYYYMM), Planned, Expected, and Phased.

Understand the difference. Currently, you need to add columns when you add months. This changes the data structure, which you want to avoid. With the alternative approach, you add data by adding rows.

Put all that in one sheet. And then in another sheet, you can roll up the data to Quarters and Years, and run your comparisons among the cost fields. Basically, the rollup will be a list of months, quarters, years, or whatever granularity you want, and you will use SUMIFs with cross sheet references to sum the data.