COUNTIFS formula across sheets

Hi all

I've been using Sheet Summary's to calculate some data which seems to work quite well. For instance this is an example of a Summary that is working fine. (Its an ISO27001 audit tracker sheet)

=COUNTIFS(Finding:Finding, "Opportunity for Improvement", Status:Status, <>"COMPLETE", [Agreed closure date]:[Agreed closure date], <TODAY())

This returns the correct value of 3

When trying to re-create the formula on say a "Workings" sheet to use within a dashboard, I'm using the following formula where I'm getting a 0 response:

=COUNTIFS({OFI-NC Finding}, HAS(@cell , "Opportunity for Improvement", {OFI-NC Status}, <>"COMPLETE", {OFI-NC AgreedCloseDate}, <TODAY()))

I have created the 3 References OFI-NC Finding, OFI-NC Status and OFI-AgreedCloseDate and are referencing the correct columns.

Any idea what I'm doing wrong and why its not giving me the correct value of 3?

All other references I'm using across sheet using COUNTIFS work fine…is it something to do with the TODAY option?

Many thanks

Matt

Find more posts tagged with

Sheets

Formulas

Dashboards

Accepted answers

Adrien Leduc

Why have you added a HAS in the formula with references?

Try without:
1. =COUNTIFS({OFI-NC Finding}, "Opportunity for Improvement", {OFI-NC Status}, <>"COMPLETE", {OFI-NC AgreedCloseDate}, <TODAY())
If you need it ok, but at the very least I think you should close the HAS before your next criteria:
1. =COUNTIFS({OFI-NC Finding}, HAS(@cell , "Opportunity for Improvement"), {OFI-NC Status}, <>"COMPLETE", {OFI-NC AgreedCloseDate}, <TODAY())

All your 3 references are from the same sheet, correct?

PS: Sometimes instead of HAS you need to use CONTAINS, but I am not good enough to say in which cases, I just do trial and errors ^^

All comments

Adrien Leduc

Why have you added a HAS in the formula with references?

Try without:
1. =COUNTIFS({OFI-NC Finding}, "Opportunity for Improvement", {OFI-NC Status}, <>"COMPLETE", {OFI-NC AgreedCloseDate}, <TODAY())
If you need it ok, but at the very least I think you should close the HAS before your next criteria:
1. =COUNTIFS({OFI-NC Finding}, HAS(@cell , "Opportunity for Improvement"), {OFI-NC Status}, <>"COMPLETE", {OFI-NC AgreedCloseDate}, <TODAY())

All your 3 references are from the same sheet, correct?

PS: Sometimes instead of HAS you need to use CONTAINS, but I am not good enough to say in which cases, I just do trial and errors ^^

Paul Newcome

Try taking one of the closing parenthesis from the very end and using it to close out the HAS function after the "Opportunity for Improvement".

Paul Newcome

HAS is designed for multi-select whether that be contact or dropdown type columns. It is used to evaluate each selection. This allows you to count for a specific option that could also be contained in another option.

CONTAINS only searches for the indicated text string.

For example… If you have a multi-select dropdown, and my three options are "This Thing", "Not This Thing", and "Something Different".

If you use CONTAINS to search for "This Thing", it will count cells that have "Not This Thing" selected because "This Thing" is part of the entire string.

If you use HAS, it will not count cells that have "Not This Thing" selected because it is not a match on the option.

Lets add more options to that multi-select dropdown: "Thing One", "Thing Two", "Don't Use This".

If a user selects "Don't Use This" and "Thing Two", the CONTAINS function searching for "This Thing" will grab it because the string made by the two contains "This Thing" right in the middle.

The HAS function won't count it because the "This Thing" option was not selected.

The CONTAINS function also does not play well with contacts. Generally speaking, HAS is the way to go when evaluating contact type columns (the FIND function works on them too, but that's a different type of thing).

CONTAINS is best used when you want to determine of a string contains a sub-string anywhere within in. HAS is for multi-selects.

MattB_ADS

Im not a formula expert and had the Ai function help me with the formulas, thats why i think the HAS function was used. Thanks 1a above worked just fine.

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.