SOLVED

New Contributor

# using =countif when searching for the asterisk "*" character

Hi all. I'm new here and really could use assistance with a formula in Excel.  I work in an attendance office at a school.  Every day I generate a spreadsheet from our attendance software and it contains A* and T* (the * does not represent a wildcard).  I need to count the instances that "A*" appears, the actual asterisk character rather than a wildcard.  I found this formula somewhere on a help website    =SUM(LEN(A6:J6)-LEN(SUBSTITUTE(A6:J6,"*","")))

However the problem with the formula is it also counts the instances of "T*".  I only want to count the "A*".  I tried adding "A" to the formula and get an error.  I tried adding a tilde ~ character before the asterisk as suggested on a Microsoft Excel help page, but a "0" appears in the result column.   : (

here's the help page I referenced about the tilde character: https://support.microsoft.com/en-us/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34

Can anyone help?

3 Replies
best response confirmed by S_M_D (New Contributor)
Solution

# Re: using =countif when searching for the asterisk "*" character

That could be

``=SUMPRODUCT(--(LEFT(A2:J2,2)="A*"))``

# Re: using =countif when searching for the asterisk "*" character

@Sergei Baklan This is awesome!  Thank you so much! I will not hesitate to post here if I need help again!