Solved Need help with COUNTIF function on Excel please

Microsoft ★ microsoft office 365 home...
December 2, 2019 at 04:21:13
Specs: Windows 10
I am trying to write a formula that counts cells that contain the code "au" which I have done with the following formula:
However, this only works if there is no other data in the cell. The cells i am working with will contain data such as "1.0au" or "0.5au" etc, but I only want to count the occurances of the "au" code, ignoring any other data in the cell.

Can anyone help with this?

Thanks in advance.

See More: Need help with COUNTIF function on Excel please

December 2, 2019 at 07:01:54
I am not the best with formulas but here is my attempt

=COUNTIF(A1:A8,"*" & "au" & "*")

Probably not the best way to do it but this is the best I can do with a formula, VBA however, if you are interested let us know.

Report •

December 2, 2019 at 07:15:14
✔ Best Answer

Nice formula, only comment is you do not need to combine the wildcards, this works also:



Report •

December 2, 2019 at 07:24:58
Another thought.

Don't really need the trailing wildcard, as it appears the OPs data always ends with au
so this also works:


it also eliminates any spurious data from being counted, IE 0.5aux or 1.0au6


Report •

Related Solutions

December 2, 2019 at 11:01:38

I was hoping you would jump in, im not good with forumlas and am still learning, many thanks for the correction and explanation.

Report •

December 3, 2019 at 02:05:56
Thanks for your help guys, much appreciated!

Report •

December 3, 2019 at 05:22:05
I think Mike and DerbyDad - to name but two at least of those gurus here - ought to start a "how to use Excel website" with tutorials galore. They (along with several others here) have a huge and in-depth knowledge about it.

Report •

December 3, 2019 at 06:50:04

Thanks for the kudos. I'll get that website started as soon as the 27 hour day goes into effect. ;-)

message edited by DerbyDad03

Report •

December 3, 2019 at 07:59:33

I also thank you for the kudos, as for the website, I already have one it's called Computing.Net.

I get all the complements with none of the heavy lifting required to maintain a website. :-)
besides, my wife probably won't let me do it. ;-(


Report •

December 3, 2019 at 08:17:20
Haha well I for one am very grateful to have somewhere I can get questions answered when I cant figure out the solution from google!

Thanks again though your solution allowed me to figure out some other cool tricks I wouldnt have known to try. My spreadsheet is functioning beautifully now :D

Report •

Ask Question