May 09, 2004

Making it all Add Up, Part 2
Previously we looked at SUM and COUNT functions. You can also use SUMIF and COUNTIF to add or count based on criteria of your choice.

Let's say you want to add sales figures from ABC Co., Ltd. Look at cell A13, you can see I have used ranges A2:A11 and B2:B11 with the criteria "ABC Co., Ltd.".

You can also use cell references instead of writing text for the criteria. In this instance, I have used cell C2 to replace "ABC Co., Ltd.".

COUNTIF works the same way as SUMIF but the second range (B2:B11) is omitted.

Simple Arrays
It's also possible to use an array formulas for SUMIF such as =SUM(IF(A2:A11=C2,B2:B11)). Enter the formula then push Ctrl, Shift and Enter at the same time to make it an array formula. (2 braces will appear automatically at either end of the formula)

You make your own combinations such as COUNTIF, AVERAGEIF, MINIF and MAXIF.

Sure beats doing it all the hard way!

This page is powered by Blogger. Isn't yours?