Countif

Hi,

See if ofllowing formula will give You the solution:

=SUMPRODUCT((A1:A5="01")*(B1:B5<1))

Kind regards,
Dennis

Dennis,

I am intrigued by the formula you posted above (which seems to work very well, BTW). It seems to incorporate criteria in a way that I have not seen before (e.g. (A1:A5="01")). I did not know that it was possible to do that where you would normally just specify a range or array.

Where else can this be used and what limitations are there?

DuckBill

Quote

Originally posted by Anonymous
I need to countif, e.g.

Column A Column B
01 .5
01 .7
01 1
02 .3
02 2

I need the result as follows:

If Column = 01 then count how many cells in Column B is less than 1

Display More

looking at the first part of the sumproduct solution : A1:A5="01"

A1:A5="01" is boolean - ie the statement is either TRUE or FALSE for each : in this case :

TRUE
TRUE
TRUE
FALSE
FALSE

or, since TRUE=1 and FALSE=0 :

1
1
1
0
0

looking at the second part :

B1:B5<1

this, again, is boolean - the statement is either TRUE or FALSE : in this case :

TRUE
TRUE
FALSE
TRUE
FALSE

and again, since TRUE=1 and FALSE=0 :

1
1
0
1
0

since there are two conditions, we take the products of both criteria in this array

1x1
1x1
1x0
0x1
0x0

equals

1
1
0
0
0

then we are summing their results :

1+1+0+0+0 = 2

so the net result is 2

2 conditions conform to the criteria we specified

this is how we bring back a "count", utilising the boolean logic of sumproduct

once you get your head round it, give us a shout and you'll see how we also use it to bring back a SUM of that count, so rather have it bring back 2, it can bring back the total values of those 2 - it's very flexible....

Dennis - I'm absolutely no expert on this, so any comments / additions are welcomed

caveat : (!)

once you discover SUMPRODUCT, it seems like a gift from heaven

however imagine having 10 criteria (ie names that begin with "B", in the "North", where the value is less than $5000, with a date in January, for salesman "Bloggs", for company "ABC Corp", etc etc etc" and you're interrogating 5,000 records....

the sumproduct will interrogate each record (all 5000 of them) ten seperate times, to ascertain whether each criteria is in fact TRUE or FALSE.... then when it's got all these, it multiplies them all together 5,000 x 10 calculations, then adds up the results....

with such a query, I wouldn't be surprised if it took a minute or so to calculate

so while it's useful, if you beging to notice performance degredations on your worksheets as you use them more often, you may be better off looking at the range of Database formulae (in the functions sections, but all preceeded with the letter D.... DSUM, DGET, DCOUNTA etc etc) which basically do the same thing but are much easier to set up and maintain, and, from experience, are a lot quicker