Countif visible cells only with exact match on text

[RCBricker]

Hi all. I have the following formula. the only problem is that there are 4 criteria and all of them have the word Matched in the options. I need a formula (which this one does all but the last part) that is dynamic to filtered data by counting the visible cells only that meet an exact text.

The text are

Matched
UnMatched
UnMatchedGL
UnMatchedTB

This formula counts all instances since each instance contains "Matched".

any thoughts?


Thanks in advance

 

[Johnny C]

There's no formula...

 

[RCBricker]

just noticed that...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B18,ROW(B2:B18)-MIN(ROW(B2:B18)),,1)),ISNUMBER(SEARCH("Matched",B2:B18))+0)

 

[Johnny C]

I'm not near a PC at the moment, but couldn't you just replace

ISNUMBER(SEARCH("Matched",B2:B18))+0

with - - (B2: B18="Matched)

Using Search will bring back a value where Matched is a substring, if you have to use Search you'd need

ISNUMBER(SEARCH("Matched",B2:B18))+0, NOT(ISNUMBER(SEARCH("*? Matched",B2:B18))) +0, NOT(ISNUMBER(SEARCH("MATCHED? *",B2:B18) ))+0),NOT(ISNUMBER(SEARCH("*? MATCHED? *",B2:B18)))+0)
)
i. e. Include anything with Matched but exclude anything with Matched in where there's 1 or more characters before, after or before and after

Apologies if my syntax is whacked I'm typing this on my phone and autocorrect is having fun.

 

[Johnny C]

PS where I put - - if that throws an error because of blank cells or something use *1 or +0

 

[Aladin Akyurek]

What is the expected count for the sample in post #1 ?

 

[RCBricker]

Based on the OP if counting Matched the answer should be 1.

 

[RCBricker]

Johnny,

That's got it! Thanks for the help.

 

[Aladin Akyurek]

Based on the OP if counting Matched the answer should be 1.

Something like:

=SUMPRODUCT(--ISNUMBER(SEARCH("|"&C2&"|","|"&$A$2:$A$5&"|")))

where C2 houses Matched and A2:A5 is the target range.