#CIRCULAR REFERENCE with =SUMIF Formula

kelceyg

For each month, I am trying to sum up data depending on the type of project it is. For example, "If the 'Project Type' is 'Strategic', this is the total hours for the month of February," "If the 'Project Type' is 'Operational', this is the total house for the month of February."

These the formulas I tried using:

=SUMIF([Project Type]:[Project Type], "Strategic", [Month 1]:[Month 1])

=SUMIF([Project Type]:[Project Type], "Operational", [Month 1]:[Month 1])

I kind of understand why I am getting the error codes, but I also don't understand because I have different criteria I want the formula to look at before providing the sum.

(I've blocked out the project names just to cover my bases)

I am new to Smartsheet, so please advise on how to fix these formulas or if I'm using the wrong formula altogether.

Genevieve P.

Hi @kelceyg

The formulas themselves are fine, and are structured correctly. The issue is the column that you're entering them in to. You're putting the formula in the [Month 1] column but you're also referencing the entire column in your formulas:  [Month 1]:[Month 1]

You can have one formula in it's own column referencing the entire thing and it will ignore itself in the calculation, ex. SUM(Column:Column). However as soon as you add 2, you'll receive a circular reference error (see the section titled "Reference a Whole Column" in this article: Create a Cell or Column Reference in a Formula)

If you want to use an entire column reference, I would suggest adding your formulas into a new, helper column that's off to the side from the data you're Summing. Either that, or you could use row references in your range to have the formula start after your top summary formulas, like so:

 [Month 1]9:[Month 1]150

If your plan has access to it, the Sheet Summary section of a sheet is a great place to put these types of summary formulas, see: Define Your Work With Sheet Summary. This will keep them out of the column so you can reference the entire thing without causing an error.

Cheers,

Genevieve

Genevieve P.

Hi @kelceyg

The formulas themselves are fine, and are structured correctly. The issue is the column that you're entering them in to. You're putting the formula in the [Month 1] column but you're also referencing the entire column in your formulas:  [Month 1]:[Month 1]

You can have one formula in it's own column referencing the entire thing and it will ignore itself in the calculation, ex. SUM(Column:Column). However as soon as you add 2, you'll receive a circular reference error (see the section titled "Reference a Whole Column" in this article: Create a Cell or Column Reference in a Formula)

If you want to use an entire column reference, I would suggest adding your formulas into a new, helper column that's off to the side from the data you're Summing. Either that, or you could use row references in your range to have the formula start after your top summary formulas, like so:

 [Month 1]9:[Month 1]150

If your plan has access to it, the Sheet Summary section of a sheet is a great place to put these types of summary formulas, see: Define Your Work With Sheet Summary. This will keep them out of the column so you can reference the entire thing without causing an error.

Cheers,

Genevieve