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?

Find more posts tagged with

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.