COUNT IF, INDEX, MATCH

I have one table with a reference number, a sub-vessel number and a check box column to select the desired subvessel. I want to use a formula in another sheet to count the number of times a value is present in SOURCE(subvessel selection column), where the SOURCE(reference number) matches the TARGET(reference number) and if this value is >0, return a 1, otherwise return 0.

So far, I have this: =IF(COUNTIF(INDEX({Source Sub-vessel select}, MATCH([reference@row,{Source reference}, 0)), {Source Sub-vessel select}:{Source Sub-vessel select}, >0), 1, 0)

but I'm getting an UNPARSEABLE ERROR. Can anyone help fix it?

It's complicated by the fact that the reference number is duplicated within the source sheet, so I can't used INDEX MATCH because the identifier isn't unique.

Thank you!

Find more posts tagged with

Formulas

Accepted answers

All comments

Philip Robbins

Hi @ZJA,

{Source Sub-vessel select}:{Source Sub-vessel select} doesn't look right to me. If the column is in the same sheet it should be [Source Sub-vessel select]:[Source Sub-vessel select], if it's a cross-sheet reference it should be {Source Sub-vessel select}.

Paul Newcome

You would just use a COUNTIFS.

ZJA

Thanks @Philip Robbins - I tried =IF(COUNTIF(INDEX({Source Sub-vessel select}, MATCH([reference@row,{Source reference}, 0)), {Source Sub-vessel select} >0), 1, 0) and it is actually coming up with a number which is nice, but it seems to be missing some values? I think it might not be picking up some cases where there are duplicated references and only 1 is selected but I'm not sure.

Philip Robbins

@ZJA it's a little hard to debug it without seeing a couple of snips of your sheet. Assuming you have two criteria to search on, then, as pointed out by @Paul Newcome, you would use a COUNTIFS to collect the number of rows that match the criteria and then wrap them in the IF statement. Something like:

=IF(COUNTIFS({Source Sub-vessel select}, [sub-vessel]@row,{Source reference},reference@row)>0), 1, 0)

ZJA

Thanks very much @Philip Robbins - I'll try that and if it doesn't resolve it, I'll try and send some snips

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.