Help with Sheet Summary Formula

Kblake

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?

Nic Larsen

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

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

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.