Countif (with two criteria from another sheet)

I'm try to count only if rows from another sheet match two criteria in two columns.

The sheet is Findings list, and the columns are below.

What am I doing wrong?

=COUNTIFS({Findings List Range 2}:{Findings List Range 2}, <>"Complete" , [{Findings List Range 1}]:[{Findings List Range 1}], <Today())

Find more posts tagged with

Accepted answers

Mike Wilday

OOOOH. And the cross sheet formulas shouldn't be in a range themselves.

=COUNTIFS({Findings List Range 2}, <>"Complete" , {Findings List Range 1}, <Today())

I don't use them often enough to notice but your final result should look like that.

All comments

Mike Wilday

Are you getting an error message?

Pete Cardenas

Unparseable

Mike Wilday

=COUNTIFS({Findings List Range 2}:{Findings List Range 2}, <>"Complete" , {Findings List Range 1}:{Findings List Range 1}, <Today())

Try this one. You had some unnecessary brackets in there [ ]

Pete Cardenas

Hmmm, still unparseable

Pete Cardenas

So the row I'm trying to count is "findings list range 2", but only if the due date is greater than today. I have an example of it.

Hence for the above, I should only count 1 row as overdue for today. This of course is being called in a different "metrics" sheet to be used on a dashboard.

Mike Wilday

When you do your countifs ranges in the cross-sheet selection are you selecting one column at a time?

You should create each range separately and only select the one column that you are checking against. Then create the second range with only one column that you are working with. Try recreating those cross-sheet formulas and let me know if that worked.

Mike Wilday

OOOOH. And the cross sheet formulas shouldn't be in a range themselves.

=COUNTIFS({Findings List Range 2}, <>"Complete" , {Findings List Range 1}, <Today())

I don't use them often enough to notice but your final result should look like that.

Mike Wilday

That is probably the issue.

Pete Cardenas

Ok I'm trying to figure this out. Thank you for assisting!

I'm trying to understand what you mean by "selecting one column at at time" and "should not be in a range by themselves". I assumed if I plug in the formula about it'll work.

Let me do the play by play:

Go to "metrics" sheet
Type in "=countifs"
This brings up the prompt. After that I go to "reference sheet" and then select the column (not sure if you mean a range is the same as the column) I just select the top row of the column and it gives me the "Findings list Range 2".

Pete Cardenas

Nevermind! The formula worked! (

Pete Cardenas

Thank you so much!

Mike Wilday

Awesome. Glad i could help out.

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.