Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to have a variable cell reference across sheets? | Excel Discussion (Misc queries) | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
Can the column index in a cell address be made variable? | Excel Discussion (Misc queries) | |||
Linking a cell to another workbook cell based on a variable name | Excel Discussion (Misc queries) | |||
Checking if a cell is filled up with data | Excel Discussion (Misc queries) |