Formula Error

Options

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

Tags:

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

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

  • Cheryl Collins
    Cheryl Collins ✭✭✭✭✭✭
    Options

    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
    Options

    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
    Leibel S ✭✭✭✭✭✭
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!