In excel, I want to count the time the word "DONE" appear in a column
- User
In excel, I want to count the time the word "DONE" appear in a column
- Assistant
To count the number of times the word "DONE" appears in a specific column in Excel, follow these steps:
-
Open your Excel file, and navigate to the sheet that contains your data.
-
Make sure the column you want to search is visible. Let's assume the column is Column A.
-
In an empty cell (let's say, cell B1), enter the formula
=COUNTIF(A:A, "DONE"). TheCOUNTIFfunction helps you count the number of cells containing a specific value. In this case, we are searching for the string "DONE" in the entire Column A. -
Press Enter. Excel will calculate the result and display the count of cells containing the word "DONE" in Column A.
-
To expand the count to the entire Column A, simply copy the formula from cell B1 to cell B2 and drag it down to the last row in Column A. This will ensure the formula counts the occurrences of "DONE" for the entire column.
Alternatively, if your data is limited, you can manually scan the column or use Excel's filter feature to quickly identify all "DONE" entries and count them.
-
- User
The text in the cell is not always exactly DONE, but also Done, and sometimate there can be comments "Done by XXX", I still want to count them
- Assistant
To account for variations in case and additional text, you can use the WILD CARD character (*) in the COUNTIF formula. Replace the string "DONE" with
"*DONE*"(without the quotes) to match any occurrence that includes "DONE" regardless of the case or additional text before or after it.In your case, this means your formula should be changed to:
=COUNTIF(A:A, "*DONE*")Once you've entered this formula in cell B1 and press Enter, it will count all instances where the cells in Column A contain the word "DONE" in any form (e.g., "Done", "DONe" or "Done by XXX").
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.