CountIF with wild card

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!

Find more posts tagged with

Formulas

Legal

Project Management

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.