Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

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

Copper Contributor

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 (Copper Contributor)
Solution

@S_M_D 

That could be

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

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

@S_M_D 

Sure, ask your question, we are glad to help

1 best response

Accepted Solutions
best response confirmed by S_M_D (Copper Contributor)
Solution

@S_M_D 

That could be

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

View solution in original post