R
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
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