Countif reference range

I would like to know when in January an issue was logged. Both my month and Issues are referencing another sheet. I want a count of January issue looking at columns Issue 1 through column Issue 4 (four column range).

This works - =COUNTIFS({Month}, "January", {Issue #1}, "002_Bypass Audit")

Does not work - =COUNTIFS({Month}, "January", {Issue #1}, "002_Bypass Audit"), {Issue #2}, "002_Bypass Audit"), {Issue #3}, "002_Bypass Audit"), {Issue #}, "002_Bypass Audit")))

Does not work - =COUNTIFS({Month}, "January", {Issue Range 1-4}, "Research Audit Issue")

Find more posts tagged with

Accepted answers

bisaacs

@Joey135 That's really odd. There's something wrong with the column as a whole then. Have you tried deleting the column and re-adding it to see if it works (if doing this isn't too much of a hassle>)

All comments

bisaacs

Hey @Joey135,

It looks like the 1st formula you list that isn't working has too many closed off parenthesis in it and it should only have 1 open and 1 close parenthesis in the formula. It should look like this:

=COUNTIFS({Month}, "January", {Issue #1}, "002_Bypass Audit", {Issue #2}, "002_Bypass Audit", {Issue #3}, "002_Bypass Audit", {Issue #}, "002_Bypass Audit")

Hope this helps!

Joey135

@bisaacs Is there a way to make this version work?

=COUNTIFS({Month}, "January", {Issue #1}, [Primary Column]@row, {Issue #2}, [Primary Column]@row, {Issue #3}, [Primary Column]@row, {Issue #4}, [Primary Column]@row)

I tried yours and I got invalid ref.

bisaacs

Hey @Joey135,

If you're getting an invalid reference that means one of the columns/rows you're trying to use in the calculation doesn't exist. You'll want to make sure that all the data points you're trying to check are valid

Joey135

@bisaacs I see what happened. I tried again and it returns a 0 when I know there are four. Do you see any reason why my below formula shouldn't work?

=COUNTIFS({Month}, "January", {Issue #1}, [Primary Column]@row, {Issue #2}, [Primary Column]@row, {Issue #3}, [Primary Column]@row, {Issue #4}, [Primary Column]@row)

bisaacs

Hey @Joey135,

For COUNTIFS function all criteria have to be met for it to add to the count. All the columns have the same type and all the data is the same?

Joey135

@bisaacs Yes they all contain text that I can put in " " or I have a column that is referencing what I am looking up Error@row. I can get =COUNTIFS({Month}, "January", {Issue #1}, Error@row) to work but if I try to add the other Issue columns (2-4) it doesn't work.

Also, in my Issue #3 column I have a Error@row with 021_Market IDs that I know has one in it and it will not count it. I keep getting a 0.

Working sheet:

Joey135

@bisaacs My Issue 3 column will not add up at all. Here are supporting screen shots. I will not pick up anything in Test Issue 3 column. I re-created this for February, and it did the same thing. All worked 1, 2, and 4 but not 3.

Working sheet:

Reference Sheet:

bisaacs

@Joey135 That's really odd. There's something wrong with the column as a whole then. Have you tried deleting the column and re-adding it to see if it works (if doing this isn't too much of a hassle>)

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.