Menu
Currently I'm using this formula to count the number of string matches in a range:
=COUNTA(FILTER(D3:D723,FIND(A1, D3:D723)))
If A1="am" and range has "ham", "scammy", "pan"; then the cell will display 2.
It appears to work correctly, except for one thing- it displays a match of 1 if there are no matches. How can I fix this?
660
You can use the =UNIQUE() and =COUNTIF() functions to count the number of occurrences of different values in a column in Excel.
In Excel, I can tell you some simple formulas to quickly count the occurrences of a word in a column. Select a cell next to the list you want to count the occurrence of a word, and then type this formula =COUNTIF(A2:A12,"Judy") into it, then press Enter, and you can get the number of appearances of this word. Note: 1.
You can use:
=COUNTIF(D3:D723,"*"&A1&"*")
It counts the amount of cells in D3:D7243 that have the substring in Cell A1. The * are there as a wildcard in front and behind the substring.
I made a working example for you as well to look at.
UPDATE: fixed statement
73
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
