Formula for counting the number of occurrences within a range of cells AND Strings help

Hi,

As the title stats, I'm looking for the easiest way to count the number of occurrences within a cell range.

The formula that I'm currently using is:

=COUNTIF(D$5:D$8,"a*")

This counts the number of cells that start with 'a' and returns the sum. It seems to work fine, but when I try to make it look for more values in the range it gives me an error. For example;

When I want to find multiple values in the range and count them all, I use this formula:

=COUNTIF(D$5:D$8,OR("a*","b*","c*"))

Anyone know what I'm doing wrong?

Try using the countifs function

Look here-->http://www.techonthenet.com/excel/formulas/countifs.php

How about:

Please Login or Register  to view this content.

Try this

=SUM(COUNTIF(D5:D8,{"a","b","c"}))

Originally Posted by alansidman

Try using the countifs function

Look here-->http://www.techonthenet.com/excel/formulas/countifs.php

I tried using countifs like this:

=COUNTIFS(D$5:D$8,"a*",D5:D8,"b*",D5:D8,"c*")

It doesn't seem to work.

Originally Posted by ConneXionLost

How about:

Please Login or Register  to view this content.

This should work, but I want to see if there is an shorter way of doing this.

Originally Posted by AlKey

Try this

=SUM(COUNTIF(D5:D8,{"a","b","c"}))

This didn't work either.

Try this

=SUM(COUNTIF(D5:D8,{"a*","b*","c*"}))

Originally Posted by AlKey

Try this

=SUM(COUNTIF(D5:D8,{"a*","b*","c*"}))

Tried this too, but it only counts 1.

The result I'm looking for would be similar to the formula:

=COUNTIF(D$5:D$8,"a*")+COUNTIF(D$5:D$8,"b*")+COUNTIF(D$5:D$8,"c*"

******

I typed it in wrong, you're right alkey, this work. +1

You are wrong

in this case formula is:

=SUM(COUNTIF(A2:A11,{"a*","b*","c*"}))

	A	B
2	ark	7
3	amon
4	mark
5	stone
6	bond
7	bottle
8	board
9	apple
10	grass
11	cob

Glad it worked for you

Thanks for the feedback!

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).