#CIRCULAR REFERENCE with =SUMIF Formula
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.
Best Answer

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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Answers

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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!