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:
=COUNTIF(F3:BL3,"au")
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

Reply ↓  Report •

#1
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.


Reply ↓  Report •

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

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

=COUNTIF(A1:A10,"*au*")

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#3
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:

=COUNTIF(A1:A10,"*au")

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

MIKE

http://www.skeptic.com/


Reply ↓  Report •

Related Solutions

#4
December 2, 2019 at 11:01:38
Mile,

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


Reply ↓  Report •

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

Reply ↓  Report •

#6
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.

Reply ↓  Report •

#7
December 3, 2019 at 06:50:04
trvlr:

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

message edited by DerbyDad03


Reply ↓  Report •

#8
December 3, 2019 at 07:59:33
trvlr:

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. ;-(

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#9
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


Reply ↓  Report •

Ask Question