Forum Discussion

andie200's avatar
andie200
Copper Contributor
Nov 06, 2019

countif exact text

Hi

I have a formula that I am having issues with.

My current formula is :    =countif($D18:$018,"*"&AC$13&"*")

 

AC13 = P1

D18-O18 is my range of cells

 

However this also counts cells that contain the text P11 for example. What do I need to do to the formula so that it only counts cells with P1?

Thanks for your help

Formulas and Functions

2 Replies

Sort By
  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Nov 07, 2019

    andie200 

    If you want to count instances of both P1 and p1, use COUNTIF like this: 

    =COUNTIF(D$18:O$18,AC13)

    Nonetheless, use any of these if you want to count instances of P1 only and exclude p1: 

    1. Array formula (confirmed with Ctrl+Shift+Enter): 

    =SUM(--EXACT(D$18:O$18,AC13))

    2. Non-array formula (confirmed with regular Enter): 

    =SUMPRODUCT(--EXACT(D$18:O$18,AC13))

    The choice is yours!

  • mathetes's avatar
    mathetes
    Silver Contributor
    Nov 06, 2019
    Not knowing what the other "P1*" combinations might be....your function is actually performing as a very literalistic computer would be expected to; after all, "P11" does contain "P1"---

    So, given that fact, a simple solution for the immediate short term--may not be suitable if this is a long term, repeat need-- is to ALSO do a similar COUNTIF but this time count the cells that contain "P11", presumably a smaller number, and just subtract the latter from the former.

    It's not the most elegant solution, but it would give you the number you seek. To dig into it further would probably involve just as much work, creating some "helper columns" with various forms of text manipulation. But they'd all amount to the same thing, basically....

Resources