Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum if not null
I would like to sum all cells which are non-blank (same as selecting non-blank
cells in a filter). I tried =sumif(A:A,"<""""",B:B) but Excel didn't think much of it. What is the correct criteria for 'not null'? -- Regards, Fred |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum if not null
Fred,
Perhaps try something like: =SUMPRODUCT(--(A1:A100<""),B1:B100) Adapt the ranges to suit. We can't use entire col refs in SP. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Fred Smith" wrote in message ... I would like to sum all cells which are non-blank (same as selecting non-blank cells in a filter). I tried =sumif(A:A,"<""""",B:B) but Excel didn't think much of it. What is the correct criteria for 'not null'? -- Regards, Fred |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum if not null
Try this:
=SUMIF(A:A,"<"&"",B:B) "Fred Smith" wrote: I would like to sum all cells which are non-blank (same as selecting non-blank cells in a filter). I tried =sumif(A:A,"<""""",B:B) but Excel didn't think much of it. What is the correct criteria for 'not null'? -- Regards, Fred |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum if not null
It seems to work okay w/o the &"".
=SUMIF(A:A,"<",B:B) "Teethless mama" wrote: Try this: =SUMIF(A:A,"<"&"",B:B) "Fred Smith" wrote: I would like to sum all cells which are non-blank (same as selecting non-blank cells in a filter). I tried =sumif(A:A,"<""""",B:B) but Excel didn't think much of it. What is the correct criteria for 'not null'? -- Regards, Fred |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum if not null
=SUMIF(A:A,"<",B:B)
Above doesn't seem to return correctly if col A contained null strings: "", which was what I thought Fred was looking for. That's why I suggested the good, old Sumproduct way <g. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum if not null
I see what you mean. Good call. I'll have to try to remember that
particular "feature". <g "Max" wrote: =SUMIF(A:A,"<",B:B) Above doesn't seem to return correctly if col A contained null strings: "", which was what I thought Fred was looking for. That's why I suggested the good, old Sumproduct way <g. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
visual basic within Access If...Then | Excel Discussion (Misc queries) | |||
visual basic within Access If...Then | Excel Discussion (Misc queries) | |||
VLookup Null values | Excel Worksheet Functions | |||
Null values in charts and how to override the goal seek functionality | Charts and Charting in Excel | |||
Multipying columns that contail null values | Excel Discussion (Misc queries) |