Wednesday, February 27, 2013

Musings on Conditional Counts / Sums

The COUNTIFS() function allows you to use more than one criteria for the statistic. Often, we think of this as for example, counting the row entries where Country is “Australia” and State is “NSW”

e.g.

  A B C
1 Australia NSW 10
2 Australia VIC 20
3 Australia NSW 30
4 USA Alabama 40
5 USA New York 50

So in this case, there are two rows of Australia, NSW

and the COUNTIF expression is

=COUNTIF(A1:A5,”Australia”,B1:B,”NSW”)

However, we can also use multiple AND criteria on one parameter e.g. Count how many entries are between 20 and 30

=COUNTIF("C1:C5”,">=20”,”C1:C5”,”<=30”)

In a related vein, for the visual Conditional Formatting, we can create more than one Rule on a Worksheet. Often, we may use a different Rules for different parts of the Worksheet. But, there’s nothing stopping you from using more than one Rule (as long as the Rules don’t overlap in scope) on the same cell block.

conditional

In case you want to look around:

Here’s a Microsoft produced video tutorial on SUMIF and COUNTIF functions:

and a downloadable Powerpoint Presentation on nested IFs

No comments: