Blog > Sophisticated Counting in Excel
by Maria Lahiffe
A lot of questions you get from your manager, your board, and donors start with “how much” or how many”. How many clients did you serve in 2017? How many were women? How many women were seniors? How much of our annual revenue comes from young parents? You get the idea.
If you have 10 clients, you can count them yourself. (Mind you, if you have only 10 clients, you’re probably about to go out of business.) If you have 100 or 1000 clients, then get Excel to do the job for you. It will give you more accurate results, faster than any human. There are a number of count functions which are worth learning.
Format: =COUNT(Range)
Example: =COUNT(D2:D12)
This will count how many numbers are in the cell range you specify. Let’s say a number of your volunteers donate money as well as their time. Perhaps you would like to count the number of people who made a financial donation. You can find that out by counting the number of cells in the “donation” column.
Format: =COUNTA(Range)
Example: =COUNTA(D2:D12)
While =COUNT() tells you the number of cells in a range which are occupied by numbers, =COUNTA() counts the number of cells in the range which are occupied by anything.
In our example, let’s say you want to know what proportion of your volunteers have made a financial donation. You can do that by counting the total number of volunteers, say by counting the number of cells in the “last name” column.
With your total number of volunteers, it is easy to calculate the percentage.
Format: =COUNTIF(Range,Criteria)
Example: =COUNTIF(D2:D230,”>=60”)
This will count the number of cells in the specified range which meet the criterion you specify. You could use this, for example, to count the number of volunteers who are also seniors. You can do this by counting the number of cells in the “age” column which contain a number greater than or equal to 60.
Note that the criterion needs to appear within quotes. Here are some examples of criteria you could use:
To count… |
Use this criterion |
Number of females |
“F” |
Number of donations above $50 |
“>50” |
Number of people aged 20 or younger |
“<=20” |
Number of people named Bob |
“Bob” |
Number of phone numbers with the 613 area code |
“613*” |
Number of people who use Hotmail |
“*Hotmail*” |
Format: =COUNTIFS(range1,criteria1,range2,criteria2,…) up to 50 pairs
Example: =COUNTIFS(D2:D92,”F”,G2:92,”>18”,G2:G92,”<=65”)
This allows you to apply multiple criteria. For example, if you want to count the number of volunteers who are women of working age, that can be broken down into three separate criteria:
Criterion |
In Excel |
Women |
C9:C508,”F” |
Older than 18 |
E9:E508,”>18” |
Younger than or equal to 65 |
E9:E508,”<=65” |
Note that we applied two criteria to the same range. We still had to repeat the range each time.
Volunteer Ottawa offers a comprehensive suite of courses related to all aspects of running a non-profit or a charity. Click here for our event calendar. Subscribe to our Event RSS Feed to be among the first to know when a new workshop is added to the schedule.
Like what you've read? Subscribe to our RSS feed so you never miss a post! We have a general RSS Feed for all VOices blog posts, as well as an Operations RSS Feed which will focus on topics related to organizational operations.
Related Blog Posts: