countif or sum array formula

[lindasf]

Hi,

I would like to evaluate every cell (in all columns) of Row 1 that ha
the value of “2” embedded in it. These cells all contain other text
hence the use of the wildcard.

Once I identify the columns, I would like to look at the next row (ro
2 for example) and count the cells in row 2 that contain the value o
“x” (for the columns identified in step 1). Once again I’m using th
wildcard (since some users have entered "blank X" or "x blank" in th
cells.

I have tried many different versions of COUNTIF and SUM that have give
me errors.

I finally used =SUM(($D2:$CX2="*x*")*($D$1:$CX$1="*2*")) which
translates to {=SUM(($D2:$CX2="*x*")*($D$1:$CX$1="*2*"))}

This does not give me an error, but returns a value of 0, which i
incorrect.

Thanks so much in advance for your help, I have spent so much time o
this and am really stumped.

Please see cell DC2 for my formula.

Thx much.

Linda

P.S. I cut the file size down (hence the name "junk") in order to b
able to post it

Attachment filename: junk-end user - sorted by curriculum 1 & alpha subsystem - merged &sorted - 3 columns.xl
Download attachment: http://www.excelforum.com/attachment.php?postid=49595

 

[Frank Kabel]

Hi
try
=SUMPRODUCT((ISNUMBER(FIND("2",D1:CX1)))*(ISNUMBER(FIND("x",D2:CX2))))

--
Regards
Frank Kabel
Frankfurt, Germany

Hi,

I would like to evaluate every cell (in all columns) of Row 1 that has
the value of “2” embedded in it. These cells all contain other text,
hence the use of the wildcard.

Once I identify the columns, I would like to look at the next row (row
2 for example) and count the cells in row 2 that contain the value of
“x” (for the columns identified in step 1). Once again I’m using the
wildcard (since some users have entered "blank X" or "x blank" in the
cells.

I have tried many different versions of COUNTIF and SUM that have given
me errors.

I finally used =SUM(($D2:$CX2="*x*")*($D$1:$CX$1="*2*")) which
translates to {=SUM(($D2:$CX2="*x*")*($D$1:$CX$1="*2*"))}

This does not give me an error, but returns a value of 0, which is
incorrect.

Thanks so much in advance for your help, I have spent so much time on
this and am really stumped.

Please see cell DC2 for my formula.

Thx much.

Linda

P.S. I cut the file size down (hence the name "junk") in order to be
able to post it.

Attachment filename: junk-end user - sorted by curriculum 1 & alpha

subsystem - merged &sorted - 3 columns.xls