Excel 2010: COUNTIF issue

[eicherj]

Greetings from the Land of Lincoln!

I have a 47x16 table (column and row headings included) in which every cell has data, but data is not visible in all cells. This is because of a VLOOKUP statement I am using:

Columns!P2

=IF(ISNA(VLOOKUP($A2,Tables!O$1:O$20,1,FALSE)),"",VLOOKUP($A2,Tables!O$1:O$20,1,FALSE))

<tbody>
</tbody>

(no spaces between "")

My workbook has 2 sheets, Tables! and Columns! and Columns! is the current sheet.

The translation of my equation is: Take the text in Columns!A2 and look in Tables!O2:O20. If it is not found. insert "" in Columns!P2. If the value in Columns!A2 is found in Tables!O2:O20, then insert it into Columns!P2.

Believe it or not, this formula does what I need it to do in all 690 cells (excluding column and row headings).

So much for the set-up. Here's the question:

How do I count the number of columns in each row where there is visible data?

=COUNTIF(B2:P2,"<>""") should return an answer of 1 but instead it returns 15. I've tried so many variations of this statement I can't see straight anymore.

Thanks for your help,

eicherj

 

[barry houdini]

What sort of data is your VLOOKUP formula returning? For text values try this formula

=COUNTIF(B2:P2,"?*")

.....but that won't work for numbers so for numbers perhaps

=COUNT(B2:P2)

.....or for any of the above or a mixture

=SUMPRODUCT((B2:P2<>"")+0)

 

[West Man]

Also could't your formula in P2 be shortened to =IF(ISNA(VLOOKUP($A2,Tables!O$1:O$20,1,FALSE)),"",$A2)

 

[barry houdini]

Also could't your formula in P2 be shortened to =IF(ISNA(VLOOKUP($A2,Tables!O$1:O$20,1,FALSE)),"",$A2)

or possibly

=IF(COUNTIF(Tables!O$1:O$20,$A2),$A2,"")

 

[eicherj]

{=SUM(IF($B2:$P2=$A2,1,0))} is the solution.