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

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:F50))

will return the desired result.

HTH,
Bernie
MS Excel MVP


wrote in message
ups.com...
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

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

Bernie Deitrick wrote:
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:F50))

will return the desired result.

HTH,
Bernie
MS Excel MVP


wrote in message
ups.com...
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