COUNTIF with unknowns

[Hunterlock]

Hi everyone!

This one is a bit complicated so I hope you can bear with me.

I've recently learnt that it possible to have a formula that would only count if multiple criteria are met by searching vertically by playing around with COUNTIF, MATCH & OFFSET but in this case, I am finding it very difficult to do what I need.

Please review the attached file.

Thank you!

[Pepe Le Mokko]

Sorry for off-topic interjection:

Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

[Hunterlock]

Apologies, I didn't realise this would be an issue.

Below is the data I have in column A.

I am looking for a way to:

1. Search for COMPLETE in the column
2. If COMPLETE is found, match the unknown Ref which always starts with X and is 11 digits long
3. Match that Ref to a User
4. Count how many were found, so in this case, it should return 1 for each of the users.

[JeteMc]

This proposal employs two helper columns, which may be moved and/or hidden for aesthetic purposes, and two helper cells (M2:N2) which are hidden by choosing white font.
The first helper column (B) displays the name using: =IFERROR(RIGHT(A1,LEN(A1)-SEARCH("User ",A1)-4),"")
The second helper column (C) displays the Ref using: =MID(A1,SEARCH("X",A1),12)
M2:N2 are populated using: =INDEX($C1:$C18,MATCH(M$3,$B1:$B18,0))
M5:N5 are populated using: =COUNTIFS($A$1:$A$18,"*"&$L5&"*",$C1:$C18,M2)
Let us know if you have any questions.

[BMV]

Array formula

Please Login or Register  to view this content.

[Hunterlock]

Thank you JeteMC & BMV! this is great stuff.

Much appreciated!!

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.