Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional counting
Hi,
sumif(range,criteria,sum_range) is a very nice function. Is there any way I can achieve similar result with some count function, something like countblank(range, on_condition, countblank_range)? ArthurN PS countif seems a bit limiting, or is it not? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional counting
Give us an idea of what you want to do. It may well be that a UDF could be
made to do it. "ArthurN" wrote: Hi, sumif(range,criteria,sum_range) is a very nice function. Is there any way I can achieve similar result with some count function, something like countblank(range, on_condition, countblank_range)? ArthurN PS countif seems a bit limiting, or is it not? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional counting
=sumproduct() sounds like something you should look into:
=sumproduct(--(a1:a10="criteria"),--(b1:b10="")) Adjust the range, but don't use the whole column. =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html ArthurN wrote: Hi, sumif(range,criteria,sum_range) is a very nice function. Is there any way I can achieve similar result with some count function, something like countblank(range, on_condition, countblank_range)? ArthurN PS countif seems a bit limiting, or is it not? -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional counting
ps. Debra Dalgleish has lots of notes for counting/summing:
http://contextures.com/xlFunctions01.html http://contextures.com/xlFunctions04.html ArthurN wrote: Hi, sumif(range,criteria,sum_range) is a very nice function. Is there any way I can achieve similar result with some count function, something like countblank(range, on_condition, countblank_range)? ArthurN PS countif seems a bit limiting, or is it not? -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional counting
I wouldn't like to work with the numbers. I would like to find the number of
blank cells, say in column B, where the corresponding cell (row) in column A wouldn't be empty: A B 01/13 56 01/14 01/15 43 I can't use countblank(range) because I don't know the range, I know it starts at b2, I don't yet know where it ends |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional counting
=SUMPRODUCT(--(A2:A65536<""),--(B2:B65536=""))
But I think I would be more conservative and just use a row that I know won't be exceeded. If you expect 2000 entries, then make it 4000. ArthurN wrote: I wouldn't like to work with the numbers. I would like to find the number of blank cells, say in column B, where the corresponding cell (row) in column A wouldn't be empty: A B 01/13 56 01/14 01/15 43 I can't use countblank(range) because I don't know the range, I know it starts at b2, I don't yet know where it ends -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional counting with Excel | Excel Worksheet Functions | |||
conditional counting of autolist cells/rows | Excel Discussion (Misc queries) | |||
Counting the number of cells meeting conditional formating criteria | Excel Worksheet Functions | |||
conditional counting | Excel Discussion (Misc queries) | |||
counting cells with conditional formatting applied | Excel Discussion (Misc queries) |