ignore blank

  • Thread starter Thread starter vicki
  • Start date Start date
V

vicki

column a column b column c column
d
john 3, 12 2,3
peter
Mary 12, 24 26


please help with the forumula on column d to show number "1" if column a to
c not blank

thanks again to all
 
Focusing on this line alone:
.. formula on column d to show number "1" if column a to c not blank
Click to expand...

Assuming data will be entered in cols A to C in row2 down
In D2: =IF(COUNTA(A2:C2)>0,1,"")

Assuming cols A to C in row2 down
may contain formulas returned blanks: "", use this
In D1: =IF(SUMPRODUCT(--(A2:C2<>"")),1,"")
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
 
Dear Max,

what is the meaning of the "--" on the formula of
=IF(SUMPRODUCT(--(A2:C2<>"")),1,"")


thanks for your help.
 
what is the meaning of the "--" on the formula of
=IF(SUMPRODUCT(--(A2:C2<>"")),1,"")
Click to expand...

It basically coerces TRUEs to 1s, FALSEs to zeros
The TRUEs/FALSEs are evaluated by this part: A2:C2<>""

Hence: --(A2:C2<>"")
will then result in a numeric array of zeros/1s
instead of TRUEs/FALSEs for sumproduct to evaluate

Pl mark ALL responses which help by pressing the YES buttons (like the one
below)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
 
Dear Max,

thanks thank.

may i ask for help for 1 more question:
(column a *b) (counta column
c, if not blank, "1")
1 column a column b column c column d
2 3 4 12 1
3
1

for the COUNTA formula, as it also count those cell which contain formula,
since column c has formula, so on row cell D3, the column d will also show 1,
but instead of show 1, can it be a blank cell?

thanks thanks again
 
Pl go back and mark the earlier 2 responses by pressing their YES buttons.
Thanks.

As for your further query, use the SUMPRODUCT expression which was given
earlier instead of COUNTA
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
 
since column a and column b may not have number on it, sometimes only
column a has number, sometime only column b has number, and sometimes neither
a nor be has number. so when neither a nor be has number if use the
if(sumproduct (a,b),1,""), it come out #value ,can the cell is blank?

thanks again
 
Back
Top