Forum Discussion
little2fern
Mar 19, 2020Brass Contributor
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.
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
- Riny_van_EekelenPlatinum Contributor
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))- little2fernBrass Contributor
Omg you're a life saver. Thank you so much >< Have a great day!
- Riny_van_EekelenPlatinum Contributor
little2fern Glad i could help!