Complex COUNTIF

[Nogslaw]

The following formula works to diagnose cell B4:

=VLOOKUP(RIGHT(B4, LEN(B4)-IF(LEFT(B4, 1) = "@", 1, 0)), $A$42:$N$74, MATCH($B$1, $A$42:$M$42, 0), FALSE)<=$D$1

What I am looking to do, is create an array formula that will check not only B4, but every cell in B4:Q4 and tell me how many meet the criteria. I am assuming that this will be a COUNTIF, but I suppose it could be SUMPRODUCT or something else.

I'm using xl2003 (sadly)

Thanks to anyone who can help!

 

[Nogslaw]

I got the same answer both ways. Each works unless there is a duplicate.

 

[Aladin Akyurek]

I got the same answer both ways. Each works unless there is a duplicate.

Not sure I got this duplicate thing. The latter looks for duplicates in $A$42:$M$42.

Can we construct a very small sample illustrating the issue?

 

[Nogslaw]

I did get the desired result by doubling up the formula ... once looking for the teams with @ and once without. It's ugly, but here it is:

=SUM(IF(ISNUMBER(MATCH($A$43:$A$74,$B$4:$Q$4,0)),
IF(INDEX($B$43:$M$74,0,MATCH($B$1,$B$42:$M$42,0))<=$D$1,1)),
IF(ISNUMBER(MATCH(("@"&$A$43:$A$74),$B$4:$Q$4,0)),
IF(INDEX($B$43:$M$74,0,MATCH($B$1,$B$42:$M$42,0))<=$D$1,1)))

 

[Nogslaw]

Not sure I got this duplicate thing. The latter looks for duplicates in $A$42:$M$42.

Can we construct a very small sample illustrating the issue?

There are no duplicate values in $A$42:$M$42, but there will be duplicate values (CAR and @CAR) in $B$4:$Q$4