Once again I discovered a formula that I had written down in a notebook. What is it doing? Is there an easier way to solve this?
(Download my Excel file. Read my Power Query solution.)
The Formula
It’s an array entered by pressing ‘Ctrl’ & ‘Shift’ & ‘Enter’.
=SUM(N(ISNUMBER(SEARCH($C5,IF(‘Product Data’!$B$3:$B$10000=$B$5,’Product Data’!$A$3:$A$10000,””)))))
I didn’t have any data so I put sample data into sheet ‘Product Data’
Sheet ‘Product Data’
Start with SEARCH
A basic SEARCH function works like this: =SEARCH(“x”,”Texas”)
Search for “x” anywhere within text “Texas”. The answer is 3 (position where “x” is found).
But we are looking for text “Binder” within a column of text (based on a condition).
=SEARCH($C5,IF(‘Product Data’!$B$3:$B$10000=$B$5,’Product Data’!$A$3:$A$10000,””))
Sheet ‘Solutions’
‘find_text‘ $C5 search for cell C5 value “Binder”.
‘within_text‘ IF(‘Product Data’!$B$3:$B$10000=$B$5,’Product Data’!$A$3:$A$10000,”” In sheet ‘Product Data’ if column B = “Texas” get values from column A.
‘[start_num]‘ is optional. We’re not using it.
Summary in plain English:
If column B = “Texas” then look for “Binder” in each qualifying column A cell.
What Happens Inside the SEARCH array
Highlight the SEARCH array and press F9. Here’s what you’ll see (VALUE errors removed)
The first number is 42. What does it mean?
“Binder” is found in the 16th cell in the 42nd position inside that cell (A17).
Cell A17 text: “Storex DuraTech Recycled Plastic Frosted Binders”
(A16 doesn’t count as ‘State’ = “California” not “Texas”)
Almost Finished Now!
The Search array is wrapped with this: SUM(N(ISNUMBER
ISNUMBER converts the errors and numbers to TRUE or FALSE.
N converts TRUE and FALSE to 1s and 0s.
SUM adds the 1s.
When ‘State’ (column B) = “Texas”, 82 cells in ‘Product Name’ (column A) contain search word “Binder”.
Easier Solution!
Cell E5 of sheet ‘Solutions’ has this formula:
=COUNTIFS(‘Product Data’!$B$2:$B$9995,$B5,’Product Data’!$A$2:$A$9995,”*”&C5&”*”)
It’s not an array formula (faster to calculate) and it’s easier to understand.
“*”&C5&”*” wrapping cell C5 search word with wildcards does subsearch magic!
Is the Array bad?
There’s not always an easy way to solve some questions. Sometimes an array is the only way to solve it (unless you create 1000s of formula helper columns). I love arrays but I try to only use them when there isn’t an easier way to solve it.
About Me
My name is Kevin Lehrbass. I’m a Data Analyst. I live in Markham Ontario Canada.
Two years ago I was in Las Vegas for a Data software conference. There was an interesting data art object in the lobby (not related to the conference). Data and art?
That just gave me another idea for a vba post. Stay tuned!
Hi Kevin,
Obviously another good post.
Is the Array Bad ?? Nope ! It’s NOT bad !!
However, You need to appreciate the elegant of the PQ solution.
All you need do is to Filter and make changes to do code generated depending on further complexities you want to achieve. Like ignoring case, using a drop down to select the ”SearchWord” or “State” or having to search for a list of strings at the same time. The options are endless.
Thanks for sharing.
I “Stay Tuned”
NOT gonna touch the Dial 👍
Hi Kunle,
I totally agree that Power Query is an amazing tool and yes the possibilities are endless. It can do all the heavy lifting and then drop the final answer back into the sheet. Using Excel will never be the same now that Power Query (and Power Pivot) exist. The trick is knowing when you use a formula (my COUNTIFS with wildcard) and when to use Power Query. It’s good to have so many options in Excel 🙂
Thanks for reading my post Kunle!
Cheers,
Kevin
Pingback: Power Query solution: partial match count with a condition | My Spreadsheet Lab