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!
Answers
-
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}.
-
You would just use a COUNTIFS.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
-
@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)
-
Thanks very much @Philip Robbins - I'll try that and if it doesn't resolve it, I'll try and send some snips
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!