TEXT=1 formula needed

  • Thread starter Thread starter sonar
  • Start date Start date
S

sonar

Hi

I have columns, P14, AA14, AL14, AW14, BH14, BS14, CD14

Results displays in CS14

Each column (P14 - CD14), should there be any text = 1 and if blank
0

eg. P14=1, AA14=0, AL14=0, AW14=1, BH14=0, BS14=1, CD14=0

therefore CS=3

Can one formula in column CS14 accommodate this?
And if so, what format should the cells be in?

Help will be greatly appreciated
 
Whichever you meant:

=SUMPRODUCT(--(MOD(COLUMN($P$14:$CD$14)-CELL("Col",$P$14)+0,11)=0),--($P$14:
$CD$14<>""))

=SUMPRODUCT(--(MOD(COLUMN($P$14:$CD$14)-CELL("Col",$P$14)+0,11)=0),--ISNUMBE
R($P$14:$CD$14))
 
You're using confusing terms, here.

In XL descriptions, Text usually refers to non-numeric entries,
including space characters.

So "Hello" would be text, as would "1A", but 1 would not.

Blank means no entries, including spaces.

So using the normal terms, in your example none of the cells have text,
and none of them are blank.

In your example, you could select the seven cells and name them, say,
"rng", then use

=COUNTIF(rng,">0")
 
In your example, you could select the seven cells and name them, say,
"rng", then use

=COUNTIF(rng,">0")
Click to expand...
How do you NAME the cells 'rng' when they have a value inside it?
 
Do not type a name into the cells.

Select the cells then Insert>Name>Define.

Give your range a name like "mycells" or "rng"(no quotes) and OK your way out.

Gord Dibben Excel MVP
 
Back
Top