#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
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions
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
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 410 Global Discussions
 219 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 143 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 298 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!