Excel 2007 - Count Columns of Text 1

[kristal9]

Hi,

I am sure this is simple but I can't figure it out. Any help is appreciated.

I have a huge spread sheet where I want to count the number of columns that have data.

So, Column A1 should be a count of Columns B,C, D, E, &F if and only if those columns contain text content.

the result should be something like this
A b c d e f
1 blue
2 blue red
3 blue red yellow
0
5 blue red yellow orange black

Currently my formula in column A is

=countif(B1:F1,"*")

i have also tried =5-countblank(b1:f1)
and
=counta(b1:f1)

none of these work with all three formulas the result returned is 5

the problem is the contents of each of the cells contain a single quotation mark = ' so my formulas read that as text content.

I tried doing a find ' replace [blank] but then Excel can't find the ' but it finds it to count it.

so maybe i need some sort of countif not statement? dunno. It's making my head hurt.

Please help.
Many thanks!

 

[SkipVought]

the problem is the contents of each of the cells contain a single quotation mark = ' so my formulas read that as text content

WHY?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kristal9]

I don't know. I don't understand why it contains a ' mark. Even the items that contact text have that in front if you look in the cell. (e.g. 'red) I am not the original source of the data sheet. and I don't know why it reads it as text content.

I just hoping there is some way around it.

 

[SkipVought]

How about this...
[tt]
=COUNTIF(B2:F2,">'")
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlhelp]

Hi, Kristal.

Select your whole spreadsheet. Click on the Eraser icon on the right side of the Home tab, and choose Clear Formats.

If your Excel is not reporting wrong size then CTRL/END will take you to the last column and row.

I don't particularly care about apathy

 

[kristal9]

Thanks skip, I tried the formula and it gives the same results.

xlhelp... thanks too. The clear formats did indeed take out the ' but even with those removed my count function will not work.

there must be something else in the cell ??? it just doesn't make sense. if i click on the allegedly blank see and clear all then the formula will work.

any other suggestion? please?

 

[bluedragon2]

Sounds like a case of white spaces. Try TRIMing your data.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[xlhelp]

White spaces do not affect counts. Kristal, counta should work provided there is data in them thar columns.

I don't particularly care about apathy

 

[kristal9]

Hi Blue Dragon,

Yes, I have tried the Trim() and the Clean() and Trim(Clean()) and Clean(trim())

Nothing. it still simply counts the number of columns regardless of the content.

 

[xlhelp]

My apologies blue, did not realize what you were referring to. That's bacause I am not blue....

Kristal. If you hit F5 on your keyboard, click Special and choose Blanks; you will see which cells are blanks and which are not. If there are white spaces go to Replace and replace a space with nothing. The only proble is that if your data contains spaces it will clear the spaces in there as well.

I don't particularly care about apathy

 

[kristal9]

Thanks xlhelp! that was it exactly. Now my 10-countblank works.

you earned your name and a *

Many thanks. I knew it has to be simple and I was just over looking something. You totally rock and my headache is already starting to fade.