counting multiple blanks

[Double99]

Hello

I have a question to ask...

I have 14 cells all in different columns (not adjacent to one another). and I need a formula that says if all 14 cells are blank then return a value of '0'. If one or more of the cells contains data then return a value of '1'.

I'm not sure how to do this or what is the best way?

Many Thanks

 

[Matty]

Try:

=IF(COUNT(A1:D4),1,0)

Change range to suit your actual data.

Matty

 

[T. Valko]

Hello

I have a question to ask...

I have 14 cells all in different columns (not adjacent to one another). and I need a formula that says if all 14 cells are blank then return a value of '0'. If one or more of the cells contains data then return a value of '1'.

I'm not sure how to do this or what is the best way?

Many Thanks

Try something like this...

=--(COUNTA(A1,B2,C3,E4,F5,G6,H4,H10,I7,I17,J1,J12,J15,J19)>0)

Just use the correct cell references.

 

[Double99]

Try:

=IF(COUNT(A1:D4),1,0)

Change range to suit your actual data.

Matty

Thankyou...that worked a treat!

 

[Double99]

Hmmm...

Having put this into Excel, I realise that what I've asked for is slightly wrong...

What formula would I put in to return the value of '1' when ALL 14 cells contain contents, and return the value of '0' when one or more cells are blank

Sorry to be pain

Cris

 

[Matty]

If these "values" could be either text or numbers, try:

=IF(COUNTA(A1:D4)=14,1,0)

Again, change the range to suit your actual data.

Matty

 

[T. Valko]

If these "values" could be either text or numbers, try:

=IF(COUNTA(A1:D4)=14,1,0)

Again, change the range to suit your actual data.

Matty

This threw me for a loop:

I have 14 cells all in different columns (not adjacent to one another).

 

[Greg Truby]

What formula would I put in to return the value of '1' when ALL 14 cells contain contents, and return the value of '0' when one or more cells are blank

=SIGN(NOT(COUNTBLANK(«your range here»)))

or

=--NOT(COUNTBLANK(«your range here»))

ought to do the try.

 

[Greg Truby]

Well now that I look at it, editing Biff's would be easy enough as well...

=--(COUNTA(A1,B2,C3,E4,F5,G6,H4,H10,I7,I17,J1,J12,J15,J19)<>14)

The only advantage to the ones I posted above is that you needn't worry about having to change the "14" bit if the number of cells being checked for blanks changes.