Forum Discussion

little2fern's avatar
little2fern
Brass Contributor
Mar 19, 2020
Solved

Can Countifs search for criteria within a long form text?

I noticed that the data shown in D2:D6 isn't accurate.

My Goal here is to have the formula count many different ticket channels within the chosen date highlighted in yellow.

The problem is that within that range cell (picture below) that I directed the formula to look up e.g. call, is in a long text form. So I suspect that the formula only counts when the cell has only 'call' in it with no other text. 

What should I do in this case? I want the formula to count every cell range that has the specified ticket channel whether it's in a long text form or specific.

  • little2fern

    Try this one:

     

     

    =SUMPRODUCT(ISNUMBER(SEARCH(B2,'2020 All Tickets'!$E$2:$E$30000))*('2020 All Tickets'!$G$2:$G$30000>=$L$2)*('2020 All Tickets'!$G$2:$G$30000<=$L$3))

     

     

3 Replies

Resources