Microsoft 365 and Office | Excel | For home | Windows
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Hi,
What answer are you expecting for when you type Duiker?
What formula do I use
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Anonymous
I need help with an Excel formula. If I create a cell in a worksheet that has several different animal names, how do I create a formula to count the number of times one specific animal is listed. Each species I type occurs on its own line within the cell as I use Alt "enter" to create individual lines within the cell. The following is a sample of a section of my spreadsheet. Below this I have all the different possible species listed in their individual rows and I want to insert a formula that will return a value of how many times Impala occurs on any one day as an example.
| 21 | 22 | 23 |
|---|---|---|
| Duiker | ||
| Scrub Hare | ||
| Duiker | Impala | |
| Impala | Elephant | |
| Impala | ||
| Duiker <br>Dwarf Mongoose | Elephant | |
| Duiker | ||
| Duiker <br>Impala | ||
Microsoft 365 and Office | Excel | For home | Windows
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
2 answers
Sort by: Most helpful
-
HansV 462.6K Reputation points2024-08-23T08:19:32+00:00 You can use a formula such as
=SUM(LEN(range)-LEN(SUBSTITUTE(range, animal, "")))/LEN(animal)
For example, with animals listed in A4:A12, and the specific animal you want to count in K2:
=SUM(LEN(A4:A12)-LEN(SUBSTITUTE(A4:A12, K2, "")))/LEN(K2)