Counting columns with >1 instance of a specific value

[roberthofheimer]

I need to count the number of columns with a particular value in a
specific row and containing one or more instance of a specific value
within a range of rows. My data is significantly more extensive than
this, but to simplify, imagine a spreadsheet with the following data:

A B C D E F
1 red red red blue blue
2 short X X X
3 tall X X
4 round X

I need a formula (not a macro) that will calculate how many columns
have "red" in row one and an "X" in B2:F4.

The result of my formula should be 2 (because only columns B and D
contain both "red" in row 1 and an "X" somewhere in rows 2 - 4).

I started with the following array formula:
{SUM(IF(B1:F1="Red",IF(NOT(ISBLANK(B2:F4)),1,0),0))}
This returned 4, because it was counting all of the x in those columns.

How do I evaluate each column in an array individually? I thought
about NOT(ISNUMBER(VLOOKUP())), but can't figure out how to write it to
evaluate each column individually and not the entire array.

Any ideas?

Thanks,

Robert

 

[Bernie Deitrick]

Robert,

A single formula is difficult at best and impossible at worst because of the
differing row counts of the areas under test.

The best way is to use a helper row of formulas:

=COUNTIF(B2:B4,"X")

in cell B5, for example, copied to C5:F5.

Then the formula

=SUMPRODUCT((B1:F2="red")*(B5:F5>0))

will return the desired result.

HTH,
Bernie
MS Excel MVP

 

[roberthofheimer]

Nice solution. Thanks. The SUMPRODUCT function is something I need to
become more familiar with.