How to increase a variable after checking of cell is filled up or not?
 

I wanted to type a function whereby it will check if certain range of
cells are being filled in by letters/numbers. Example: If A1:C3 is not
blank, then the number of cells that are not blank will be reflected at
A4. Can I do this with excel?


--
wuming79
------------------------------------------------------------------------
wuming79's Profile: http://www.excelforum.com/member.php...o&userid=36037
View this thread: http://www.excelforum.com/showthread...hreadid=558301

How to increase a variable after checking of cell is filled up or
 

"wuming79" wrote:
I wanted to type a function whereby it will check if certain range of
cells are being filled in by letters/numbers. Example: If A1:C3 is not
blank, then the number of cells that are not blank will be reflected at
A4. Can I do this with excel?

One way ..

Try either of the 2 formulas below in A4:
=SUMPRODUCT(--(A1:C3<""))
=SUMPRODUCT(--NOT(ISBLANK(A1:C3)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

How to increase a variable after checking of cell is filled up or not?
 

Max would you please explain the working of -- in SUMPRODUCT formula. I
have read the article at xldynamic but could not understand the --
functionality.

Max Wrote:

=SUMPRODUCT(--(A1:C3<""))
=SUMPRODUCT(--NOT(ISBLANK(A1:C3)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=558301

How to increase a variable after checking of cell is filled up or not?
 

yah, I was abt to ask the same question...excel's own help file is
rather useless...


--
wuming79
------------------------------------------------------------------------
wuming79's Profile: http://www.excelforum.com/member.php...o&userid=36037
View this thread: http://www.excelforum.com/showthread...hreadid=558301

How to increase a variable after checking of cell is filled up
 

"starguy" wrote:
Max would you please explain the working of -- in SUMPRODUCT formula. I
have read the article at xldynamic but could not understand the --
functionality.

The "--" is what they call a double unary which coerces the TRUE / FALSE
returns in the comparisons evaluated within the parens to numeric 1's / 0's

Example:
=SUMPRODUCT(--(A1:C3<""))

Supposing we have inputs made within A1:C3 , say specifically in A2, B3 and
C1 (with all other cells within A1:C3 either "blank"* or empty)
*eg: there may be formulas within A1:C3 returning zero length null strings: ""

Then in A4: =SUMPRODUCT(--(A1:C3<""))

would resolve initially to:
=SUMPRODUCT(--({FALSE,FALSE,TRUE;TRUE,FALSE,FALSE;FALSE,TRUE,FAL SE}))

We can see the above happen by carefully selecting the innermost part of the
formula, viz. just the part: A1:C3<""
in the formula bar, then press F9 key

and then resolve to:
=SUMPRODUCT({0,0,1;1,0,0;0,1,0})
[ To see the above, just select the 2nd layer part viz.: --(A1:C3<"")
then press F9]

The "--" will coerce FALSEs to 0's, TRUEs to 1's
The 0's and 1's finally gets evaluated by the SUMPRODUCT
and returns in A4: 3

Try also JE McGimpsey's page for a good explanation at:
http://www.mcgimpsey.com/excel/formulae/doubleneg.html
Why use -- in SUMPRODUCT formulae

And .. Jason Morin explains it in depth in this past post
(double unary technique popularized by Harlan Grove):
http://tinyurl.com/fnt7v

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

How to increase a variable after checking of cell is filled up
 

"wuming79" wrote:
yah, I was abt to ask the same question...excel's own help file is
rather useless...

Pl see my response to starguy ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

How to increase a variable after checking of cell is filled up or not?
 

Thanks for the quick response and solution :-)


--
wuming79
------------------------------------------------------------------------
wuming79's Profile: http://www.excelforum.com/member.php...o&userid=36037
View this thread: http://www.excelforum.com/showthread...hreadid=558301

How to increase a variable after checking of cell is filled up
 

"wuming79" wrote:
Thanks for the quick response and solution :-)

You're welcome !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---