CountIF with wild card

Ajr1713

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!

Paul Newcome

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.

Ajr1713

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!

Paul Newcome

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.................)

Julie@WD

Does not work for me. I get an INVALID DATA TYPE ERROR no matter what I try.

Paul Newcome

Julie,

 

Can you provide more details in regards to your specific situation?

Sinema

I know this is a dead thread but hopefully someone sees it as I try to keep things with similar questions.

I'm probably missing something but I get an error as well

=SUMIFS({Commercial MRR}, {Commercial Service Type}, "10M", {Commercial Services}, FIND("soho", LOWER({Commercial Services}) > 0))

I also tried with LOWER(@cell) and both gave me an error.

Kelly Moore

Hi @Sinema

I moved a parenthesis from the end to in front of greater-than and swapped to @cell. I believe the formula will now work for you.

=SUMIFS({Commercial MRR}, {Commercial Service Type}, "10M", {Commercial Services}, FIND("soho", LOWER(@cell)) > 0)

If it doesn't work, reach back and we'll try again

Kelly