Conditional counting

[Guest]

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?

 

[Guest]

Give us an idea of what you want to do. It may well be that a UDF could be
made to do it.

 

[Dave Peterson]

=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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Dave Peterson]

ps. Debra Dalgleish has lots of notes for counting/summing:
http://contextures.com/xlFunctions01.html
http://contextures.com/xlFunctions04.html

 

[Guest]

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]

=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.