Formula Error
Hi
I have the following formula that should look at the number of projects in a programme RAG statuses and return back a Red if there is 1 or more project with a Red RAG, return a yellow if there is 1 or more projects with a yellow RAG, otherwise return "Green".
=IF(COUNTIFS({Project to Programme Summary | Project Status}, "Red", {Projects Intake Sheet | Programme}, [Programme Dropdown]1) > 0, "Red", IF(COUNTIFS({Project to Programme Summary | Project Status}, "Yellow", {Projects Intake Sheet | Programme}, [Programme Dropdown]1) > 0, "Yellow", "Green"))
I'm getting an incorrect argument error but, can't figure out why? The project to programme sheet reference looks at project RAG statuses of all projects in the programme and the Projects Intake Sheet matches the Programme Name to the Programme name in the Project to Programme Summary sheet.
Can anyone help?!
TIA
Cheryl
Answers
-
Do you have that error present in any cell within any of the ranges being evaluated by your formula?
-
I have screen shot the two references and as you will see from them, there are no errors
As these are both manually populated fields.
TIA
Cheryl
-
Hello, I don't see any syntax errors in your formula. The issue may be somewhere in your cross sheet reference. Are you intending to reference both sheets at once when you write
{Project to Programme Summary | Project Status}
or is that just the name assigned to your reference?If you break your formula into pieces, does the COUNTIFS by itself return an error?
COUNTIFS({Project to Programme Summary | Project Status}, "Red", {Projects Intake Sheet | Programme}, [Programme Dropdown]1)
-
Add @cell into the criteria. That should clear it up:
=IF(COUNTIFS({Project to Programme Summary | Project Status}, @cell = "Red", {Projects Intake Sheet | Programme}, @cell = [Programme Dropdown]1) > 0, "Red", IF(COUNTIFS({Project to Programme Summary | Project Status}, @cell = "Yellow", {Projects Intake Sheet | Programme}, @cell = [Programme Dropdown]1) > 0, "Yellow", "Green"))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!