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!

 

[Matty]

Could you provide a small example - and the desired result - to help us better understand what you're after?

Matty

 

[Aladin Akyurek]

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!

Would you post what you have say in B1, D1, B4:F4, B42:F42, A43:A46?

I assume A42 is actually empty or contains a header for the A-range.

By the way, why is it not $A$42:$N$42 instead of just $A$42:$M$42?

 

[Nogslaw]

I wish I could download the add-in to post my worksheet, but I can't ... I'll have to try to describe it as best as I can, and relate portions of the existing formula to the relevant sections.

Also, Aladin - good catch - it was a typo and should have been 'M' in both references, not 'N'.

The formula provided is used as a Conditional Format, and it works well to evaluate a single cell. My desired modification is to develop a worksheet formula that will evaluate how many of the cells in a particular row meet the Conditional Format. In the example below, I will be referring to cell B4 as the target of the evaluation.

B4:Q35 is a matrix of NFL football games (Row 3 above the matrix is the week number and Column A next to the matrix are the teams) All names in the matrix are 2 or 3 letters, but away games contain an additional @ symbol at the front as well. So, A4 is ARI. B4:Q4 are ARI's weekly opponents: CAR, @WAS, @SEA, NYG, etc.
RIGHT(B4, LEN(B4)-IF(LEFT(B4, 1) = "@", 1, 0)): This portion of the formula currently eliminates the @ symbol from cell B4 if it exists. The edited value of B4 then becomes the lookup value in the VLOOKUP formula
NOTE: if needed, I can eliminate the @ symbols from all of the cells, but I would prefer to keep them if possible.

$A$42:$M$74 is an array of rankings. Each team is listed once in A43:A74 (without the @ symbol), and its rank in specific categories is listed in columns B:M. Within each column, numbers 1-32 appear only once.
$A$42:$M$74 is the lookup array of the VLOOKUP formula

$B$1 contains a text value selected from a data validation list. The value will be one of the headers of the stat ranking categories described in the previous section.
MATCH($B$1, $A$42:$M$42, 0): locates the stat ranking header and uses its position in the range as the column number in the VLOOKUP formula

$D$1 contains a numeric value from 1-10
If the result of the VLOOKUP function is less than the value of $D$1, the entire formula is TRUE in regards to cell B4.

DESIRED MODIFICATION:
The current formula returns TRUE/FALSE for the evaluation of a single cell. Is it greater than $D$1 or not. My request is to alter the formula to evaluate B4, C4, D4, ... Q4 and count how many of them return TRUE values.

Confused yet?

 

[Aladin Akyurek]

Yes, I'm for I did not get what I asked for... I want to see the values the cells I listed contain. Possible?

 

[Nogslaw]

Sorry Aladin, here you go:

B1 = WR
D1 = 6
B4:F4 = CAR, @WAS, @SEA, NYG, @MIN ...
B42:F42 = QB, RB, WR, TE, K
A43:A46 = ARI, ATL, BAL, BUF ...
Your assumption about A42 is correct, it is a header for the data in column A.

 

[T. Valko]

Sorry Aladin, here you go:

B1 = WR
D1 = 6
B4:F4 = CAR, @WAS, @SEA, NYG, @MIN ...
B42:F42 = QB, RB, WR, TE, K
A43:A46 = ARI, ATL, BAL, BUF ...
Your assumption about A42 is correct, it is a header for the data in column A.

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.

Are you trying to count how many cells in B4:F4 contain "at" signs?

 

[Aladin Akyurek]

Sorry Aladin, here you go:

B1 = WR
D1 = 6
B4:F4 = CAR, @WAS, @SEA, NYG, @MIN ...
B42:F42 = QB, RB, WR, TE, K
A43:A46 = ARI, ATL, BAL, BUF ...
Your assumption about A42 is correct, it is a header for the data in column A.

Thanks.

Control+shift+enter, not just enter:

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

Hope this helps.

 

[Nogslaw]

Aladin - thanks so much for the help. It is definitely very close, but it fails to account for "duplicates" within the range B4:Q4. There will be 3 times in each row where there will be a home and away game against the same team. So it will display CAR in one cell and @CAR in another cell. The formula seems to only count this once.

I didn't think about it because my original formula only evaluated one individual cell, so this was an unimportant detail.

 

[Aladin Akyurek]

Aladin - thanks so much for the help. It is definitely very close, but it fails to account for "duplicates" within the range B4:Q4. There will be 3 times in each row where there will be a home and away game against the same team. So it will display CAR in one cell and @CAR in another cell. The formula seems to only count this once.

I didn't think about it because my original formula only evaluated one individual cell, so this was an unimportant detail.

The earlier is missing a surrounding IF I'm afraid... Anyway:

Control+shift+enter, not just enter...

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

Does it cover what we need?