How to COUNTIF with multiple criteria and cross-sheet references

I am trying to create a COUNTIFS formula to count the number of times a book title appears in a range from another sheet AND if a checkbox is checked (also from that other sheet). The cells with the book titles contain multiple titles so I've been using a CONTAINS formula to match for each book title. I can get both working separately but it breaks when I try to string them together:

=COUNTIFS({OTHER SHEET RANGE A}, CONTAINS([BOOK TITLE]@row, @cell))

=COUNTIFS({OTHER SHEET RANGE B}, 1)

But it breaks when I try to string together like this:

=COUNTIFS({OTHER SHEET RANGE A}, CONTAINS([BOOK TITLE]@row, @cell), {OTHER SHEET RANGE B},1)

The other source sheet looks like this (but with 13 different Book List columns):

The sheet that is housing this formula and the book title cell to match looks like this:

Each student will have a row in the source sheet with a book list for each course (across multiple columns). The ultimate goal is to create a count of each title, if Financial Aid is checked, so we can have a count for how many to purchase. A report doesn't seem to work since the book list columns have multiple entries. If a formula won't work, I'm open to other suggestions to meet this goal.

Thank you!

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

What exactly do you mean by "it breaks"? Are you getting an error message or an unexpected count? Are you able to provide a screenshot of the formula open in the sheet as if you are about to edit it?

Mandy and Jen

@Paul Newcome - I am mostly getting an Incorrect Argument Set error, but I sometimes got an Unparseable error with some of my attempts. Here is a screenshot of the formula which I'm getting the Incorrect Argument Set error.

Thank you!

Paul Newcome

Double check you cross sheet references. Sometimes it is possible to select a column header before the source sheet fully loads in the window. Once the source sheet finally fully loads, the selection automatically reverts back to the top right cell. Both ranges should be from the same source sheet and should be created by selecting the appropriate column header for each.

Mandy and Jen

@Paul Newcome - thanks! I think my source ranges are working properly because each half of the combined formula work on their own (also, the source sheet only has the 2 rows previously pictured). Here is each half of the formula - that is working properly on it's own:

Paul Newcome

There is nothing wrong with your syntax. Try retyping it completely from scratch in a different sheet. Sometimes the error messages can be a bit buggy.

Mandy and Jen

@Paul Newcome - I am still getting the same error message result in a new sheet. Thanks for your suggestions though!

Paul Newcome

And neither of those formulas produce an error when applied on their own?

Did you double check that both cross sheet references are referencing the same exact sheet?

Mandy and Jen

@Paul Newcome - yes, that's right. Both are working independently but not when I put them together. Yes, they are referencing the same source sheet. I know it should work together but I can't seem to get it to work. Perhaps I should just make a separate sheet for the financial aid rows (automation to move the row) and use the half of the formula I know works. Thanks again!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.