Help with Sheet Summary Formula

What I am trying to do:

I am trying to write a formula that counts the projects that are in any of the following Stage of Project: Kick-off & Requirements Gathering, Test Setup, UAT, or Deployment AND the Type of Project is Maintenance - Break Fix. 

Formula I am using:

=COUNTIFS([Stage of Project], "Kick-off & Requirements Gathering", [Type of Project], "Maintenance - Break Fix") + COUNTIFS([Stage of Project], "Test Setup", [Type of Project], "Maintenance - Break Fix") + COUNTIFS([Stage of Project], "UAT", [Type of Project], "Maintenance - Break Fix") + COUNTIFS([Stage of Project], "Deployment", [Type of Project], "Maintenance - Break Fix")

Getting a #UNPARSEABLE error.

Any suggestions on what the issue is? Or how to fix it?

Answers

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    edited 02/12/24

    Try:

    =COUNTIFS({Stage of Project}, OR(@cell = "Kick-off & Requirements Gathering", @cell = "Test Setup", @cell = "UAT", @cell = "Deployment"), {Type of Project}, "Maintenance - Break Fix"

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭✭
    edited 02/12/24

    Hi @Kblake,

    Are the columns you are looking at on the same sheet or a cross sheet reference? If these are a cross sheet reference, the square brackets should be curly brackets e.g. {Stage of Project}.

    If these are on the same sheet, the column needs to be qualified further e.g. [Stage of Project]:[Stage of Project].

    Hope this helps.

    Dave

  • Kblake
    Kblake ✭✭✭

    Thank you, both. The columns are within the same sheet. I tried the formula @Nic Larsen provided, that gave me an #INVALID REF error.

    I will play around with Dave provided to see if I can get it to work. I got an #UNPARSEABLE error, but will go back and double check I don't have an error somewhere.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!