COUNT IF, INDEX, MATCH

ZJA

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!

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