Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do you count cells with a certain partial string?

I have cells with dates that are formatted in different ways (and too messy to re-format, also lots of data) but if I want to count how many dates are in October, for example, I was hoping to do a COUNTIF() or COUNTA(FILTER()) but I'm not sure how to search the cell for the partial string 10/ (this is the only unique combination of characters that I can think of that would ID only the correct month).

like image 387
gigawatts Avatar asked Nov 05 '13 21:11

gigawatts


People also ask

How do I count cells with partial text in Excel?

Select a blank cell to store the counting result, write the formula =COUNTIF(A1:A16,"*Anne*") into it, and press the Enter key (A1:A16 is the range to count cells, and Anne is the partial string).

How do I count a cell that contains part of a text?

Type =COUNTIF( in the cell where you want to see the count. Select the range where you want to search for the text. Type a comma to go to the next argument and then type the text that should be in the cells you want to count.

How do I count certain strings in Excel?

If you want to learn how to count text in Excel, you need to use function COUNTIF with the criteria defined using wildcard *, with the formula: =COUNTIF(range;"*") . Range is defined cell range where you want to count the text in Excel and wildcard * is criteria for all text occurrences in the defined range.

How do you count cells with multiple specific text?

You can use the COUNTIFS function in Excel to count cells in a single range with a single condition as well as in multiple ranges with multiple conditions. If the latter, only those cells that meet all of the specified conditions are counted.


1 Answers

You can use * wild character, the wild character before the value means anything ending with and at the end means anything starting with...

=COUNTIF(A1:A10,"*10/*") - Has the text 10/ anywhere in the cell

=COUNTIF(A1:A10,"*"&B1&"*") - Cell B1 has the finding text

=COUNTIF(A1:A10,"10/*") - Has the text 10/ at start

=COUNTIF(A1:A10,"*10/") - Has the text 10/ at end

like image 120
Vasim Avatar answered Oct 18 '22 16:10

Vasim