# 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

#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
 AWTLNice formula, only comment is you do not need to combine the wildcards, this works also:=COUNTIF(A1:A10,"*au*")MIKEhttp://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 auso this also works:=COUNTIF(A1:A10,"*au")it also eliminates any spurious data from being counted, IE 0.5aux or 1.0au6MIKEhttp://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. ;-(MIKEhttp://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 •