If any of the cells being searched are not blank return a 1

=IFERROR(IF(AND(MATCH([Team Member]@row, {Team Member}, 0) > 0, COUNTIFS({Team Member}, [Team Member]@row, {L1 Mixer}, {L2 Mixer}, {L4 Mixer}, {L5 Mixer}, {L6 Mixer}, {L7 Mixer} <> "") > 0), "1", "0"), "Error")

It is searching for all of them returning not blank. I am wanting if any of them are returning not blank.

Find more posts tagged with

Formulas

Accepted answers

All comments

KPH

Try changing AND to OR. AND means everything must be true, OR means any are true.

Paul Mangan

If I change that to OR then it makes everything true. The AND is checking that the team members name matches the name on the other sheet. The name matches so I believe it is returning one by default and not searching for the other requirements I want.

KPH

Can you explain what your other requirements are? What is going on in the COUNTIFS? That is part of the AND and each of the criteria within it all must be true for a row to be included in the count. You can't list multiple ranges with only one criteria, if that is what is going on. Hard to tell with the cross sheet references.

Are you using data that looks like this (with some more columns between 1 and 7):

And trying to summarize where the team member has all blanks in the mixer columns? Like this:

All the rows with Gonzo and Kermit in are blank, while some of the sesame street character rows have something in, and some do not.

If this is what you are doing, I would add a column to the original data to count the non-blanks, using a COUNTIF.

=COUNTIF([L1 mixer]@row:[L7 mixer]@row, <>"")

That would give you this:

Then on your summary sheet you can use a COUNTIFS to count the number of rows where the count of non-blanks is greater than 0 and the team member matches the team member on the row, using:

=COUNTIFS({Team Member}, [Team Member]@row, {Count of non-blanks}, >0)

Which would give you this:

You can then wrap that in an IF function to return 1 for counts greater than 0, using:

=IF(COUNTIFS({Team Member}, [Team Member]@row, {Count of non-blanks}, >0) > 0, 1, 0)

Which would give you:

Hopefully this is close to what you are doing and you can adapt it. If not, can you share screen shots of your sheet and the outcome you are looking for (hiding any confidential details) and I'll have another go.

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.