This should be simple

S

Spreadsheet

I have a number of cells (say A1,A3,A5,A7,A9,A11) which may or may not
contain values. If only one of the cells contains a value, I want this
value to appear in B1. How can this be done?
 
D

Dave Peterson

If they are all all numbers:

=IF(COUNTA(A1,A3,A5,A7,A9,A11)<>1,"not exactly one!",SUM(A1,A3,A5,A7,A9,A11))

(summing empty cells won't hurt your total)

If they are all text:
=IF(COUNTA(A1,A3,A5,A7,A9,A11)<>1,"not exactly one!",A1&A3&A5&A7&A9&A11)

(Concatenating empty cells won't hurt the string)
 
C

CLR

This one formula should do it for either numbers or text.........

=IF(COUNTA(A:A)<>1,"",IF(MAX(A:A)>0,MAX(A:A),VLOOKUP("zzz",A:A,1)))

It covers the entire range of A:A but could be easily adjusted by using a
non-contiguious RangeName for your specific cells.

Vaya con Dios,
Chuck, CABGx3
 
C

CLR

Sorry, didn't test fully before posting.........the VLOOKUP part fails when
using a non-contiguious range.........my bad...<head hanging low>

Vaya con Dios,
Chuck, CABGx3
 
Top