3

I'm trying to count the number of items in a column where part of the text shows.  Is there a way to do this?

 

For e.g., I'd like it to count up the number of rows from a column in which the words "ids" show up.  Keeping in mind this only part of the text (e.g. some rows say "ids?", "ids due", "IDS? Client refs" etc."

I've tried =COUNTIF(ActionDue1:ActionDue184, "*ids*")  but it comes up as 0 because it's looking for the exact word and not part of it.

Thanks in advance!

Functionality
Industry
Department

Comments

Try this...

 

=COUNTIFS(ActionDue1:ActionDue184, FIND("ids", LOWER(@cell)) > 0)

 

This will read the entire cell (after making all text lower case to cover if it shows up in upper case) and will give a number based on where in the cell "ids" is found. The number itself is unimportant other than the fact that if "ids" is NOT found, it will return 0. Therefore if it does find it, the number would have to be 1 (first position in the cell) or greater, thus the > 0 portion for the criteria statement.

In reply to by Paul Newcome

It worked!- thank you so much!

On more quick question to further elaborate on this formula.  Is there a way to include all the information above plus for it to only include items that haven’t been completed?

 

For eg,  in another column, it is entitled “complete” with a checkbox so it would great to have a formula to count the number of times   “ids” shows up as part of the text/word but only if complete has not been checked.

Thanks in advance!

Most definitely

 

=COUNTIFS(ActionDue1:ActionDue184, FIND("ids", LOWER(@cell)) > 0, Complete:Complete, @cell = 0)

 

With COUNTIFS you need simply add another range to look at, then the corresponding criteria.

 

=COUNTIFS(Range 1, Criteria 1, Range 2, Criteria 2, Range 3, Criteria 3, Range 4, Criteria 4.................)