How do I love thee? Let me COUNT the ways

I love thee for having COUNT, COUNTIF and COUNTIFS functions. So I can summarise my data and find new insights.

I was struggling to make that work but I think you get my drift.

I won’t say the COUNT Functions are underrated. Because there is probably someone reading this who takes full advantage of them. But I will say they are underused.

What do they actually do?

COUNT Functions count thing. However, it’s important to note, COUNT, COUNTIF and COUNTIFS will count numerical values by default. COUNTIF and COUNTIFS can count non-numerical values when those values are specified.

If you just want to count text, use COUNTA. And if you want to count blank cells use COUNTBLANK.

And if you want to count the number of characters in a piece if text, use LEN.

That should have covered all bases for common count requirements. Everyone else left reading this should be interested in COUNT – who counts one thing, COUNTIF – who will count something that matches a specific criteria and COUNTIFS – who will count something that matches multiple criteria.

Let’s look at the COUNT function first

The formula is nice and simple =COUNT([select range]).

Just to say again, the COUNT function will only count numerical values.

In the example below I have attempted to count how many categories are within a department and the formula does not work

However when I use the COUNTA function it does work.

Example of COUNTA Function counting non-numerical values

COUNTIF makes it possible to count numerical and non-numerical values.

Let’s say we want to count how many options have been put on the line detail for each category.

In this example our formula would look like this:

=COUNTIF(LD!$J$11:$J$817,Sheet1!B3)

The COUNTIF Function is the step above a COUNT. A range has to be set, to define where Excel needs to count. In this example our range is on the LD Tab, column J row 11 to row 817. Then we need to tell Excel what to count.

By using a COUNTIF we can now count non-numerical values of a specific type.

COUNTIF Example

AND THEN There’s COUNTIFS

Which is the most superior option. It allows you to count multiple options based on different criteria.

So let’s say we wanted to see the different colour options for each category on our line detail.

Your formula would look like this

=COUNTIFS(LD!$J$11:$J$817,Sheet1!C$30,LD!$N$11:$N$817,Sheet1!$B31)

And you can add as many criteria as you want. Each time you just need to specify where Excel needs to count first and then what Excel needs to count. The extra criteria we added here was for colour. Which is in column N on the LD tab.

COUNTIFs Example

Oh and before I forget to say, you need to make sure your lookup ranges are the same “length”. For example here we are looking at category and colour. Both of the criteria ranges are from row 11 to 187. If I accidentally missed a row or selected one too many rows the formula would not work. I would get that lovely #VALUE! error which tells you so much about what’s wrong with the formula…

So there you have it

Soon you too will be counting the multiple ways you love (or love to hate) Excel. In the meantime if you have any questions about how you can start using the COUNT Functions, drop me a line.

Until next time.

Ax

Finding an exact match for your needs on the internet can, at times, feel like trying to find the missing  pair to your odd sock. You know it’s in the drawer somewhere, but you just can’t see it for love nor money. That’s where I can help, think of me as your Excel Agony Aunt, send in your lonely heart letters and I will find your perfect formula partner.

Have a project in mind?

I can help bring your ideas to life. Let’s talk about what we can build together.

Contact

More like this

Instagram Etsy