2 min read

Handy ways to count in Spreadsheets

Handy ways to count in Spreadsheets

This tutorial is applicable for Microsoft Excel, Google Sheets as well as Apple Numbers.

Sometimes we need to count and show the number of this and that to make a spreadsheet more useful.

For example, what if you had to find the number of names that:

  1. begin with “A”
  2. ends with “M”
  3. contains “AI”
  4. begin with “A” and ends with “B”
  5. begin with “Z” and contains a “BA”

Like that you can create any number of variations. I will show you an example of each one above to help you understand more inshaAllah.

begin with “A”

=COUNTIF(A:A,"A*")

The asterisks (*) after the “A” is including any character after the “A”.

Ends with “M”

=COUNTIF(A:A,"*M")

The asterisks (*) before the “M” is including any character before the “M”.

That means, the country name “United Kingdom” will be included in the count as the name ends with “M” and asterisks will include the rest before the “M”.

Contains “AI”

=COUNTIF(A:A,"*AI*")

Putting asterisks around the “AI” will tell the formula not to mind whatever it find before or after the “AI”. So, basically, it will look for “AI” in anywhere in the cell.

begin with “A” and ends with “B”

This one sounds a bit tricky, though it is as easy as the previous formulas.

All you want to ignore (or include whatever it may be) are the characters between “A” and “B”. So, what you have to do is, just add an asterisk in between the “A” and “B”.

That’s it.

=COUNTIF(A:A,"A*B")

begin with “Z” and contains a “BA”

Lastly what you wanna find is a name that starts with the letter “Z” and also it should have a “BA” somewhere in the cell.

Most likely it will count only Zimbabwe. You know… Mufti Menk is from that country.

For that, keep in mind that the asterisk help you ignore/include the part you don’t care about.

What matters to you are “ZBA”.

You want to start with “Z”.

You do not care about anything around “BA”, so it should be “*BA*”.

So, it should be “A*BA*”.

That’s it. Just like this 👇

=COUNTIF(A:A,"Z*BA*")

Hope you liked the tip. If you want to get more tips like this, subscribe to my blog and share the tip to help your colleagues and friends.