Formula Error

Cheryl Collins

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

Paul Newcome

Do you have that error present in any cell within any of the ranges being evaluated by your formula?

Cheryl Collins

Hi @Paul Newcome

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

SolutionSal

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) 

Leibel S

@Cheryl Collins

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"))