The COUNTIFS() Advantage

Question

In A1 thru B10:

dog	small
dog	large
cat	small
cat	large
dog	small
mouse	small
dog	small
bird	small
cat	large
flea	huge

Prior to 2007, if I wanted to count the number of small dogs,  I might use:

=SUMPRODUCT((A1:A10="dog")*(B1:B10="small"))

In Excel 2007, I can use:

=COUNTIFS(A1:A10,"dog",B1:B10,"small")

The new formula is only 38 characters (as compared to 44 in the old formula).  This may improve my productivity.

Is there any other advantage to using COUNTIFS()??

Answer 1

COUNTIFS will be considerably quicker if you use longer ranges. With XL2007 and later you can use full-column references with both those functions (handy if you don't know how much data there is), but the SP version will examine all cells within the range whereas COUNTIFS will only use the cells up to what Excel regards as the last-used row.

Hope this helps.

Pete

EDIT: Plus, you can use the wildcard characters with COUNTIFS, but with SP you would need an ISNUMBER(SEARCH... construct to achieve this, which will slow it down even more.

Answer 2

Yes....I have SP3

Answer 3

You're welcome - did you see my edit ? (maybe added after your reply).

Pete

Answer 4

Thank you !