VOices Blog Members Home Events Shopping Cart
Skip to main content
VOices

Blog > Sophisticated Counting in Excel

Sophisticated Counting in Excel

posted on 8:32 AM, February 5, 2018
Excel%2bBeyond%2bThe%2bBasics-350px.jpg

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.

=COUNT()

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.

 

Count_Excel_-1.png

Count_Excel_-2.PNG

=COUNTA()

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.

Count_Excel_-3.png

With your total number of volunteers, it is easy to calculate the percentage.

Count_Excel_-4.PNG

=COUNTIF()

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.

Count_Excel_-5.PNG

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*”

 

=COUNTIFS()

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”

 Count_Excel_-6.PNG

Note that we applied two criteria to the same range. We still had to repeat the range each time.

Like what you're reading? Subscribe to our RSS Feed so you never miss a future post!

To learn more about how to use Excel to make sense of data, come to an upcoming course

Excel 201: Pivot Tables and Charts

Click here to register Wednesday, February 28, 2018. 9:00 a.m. to 12:00 p.m.

Click here to register Wednesday, October 31, 2018. 9:00 a.m. to 12:00 p.m.

Excel 101: Introduction

Click here to register  Thursday, May 24, 2018. 9:00 a.m. to 12:00 p.m.

Click here to register Wednesday, September 19, 2018. 9:00 a.m. to 12:00 p.m.

Related Blog Posts:

 

Filed under: Operations
new comment comment