I am attempting to use a SUMPRODUCT formula to count non-exact matches in a cell range, dependent upon the month and year in a list drop down.
Please see attached.
Book1.xlsx
I am fine using exact matches with the following:
I thought the following would work but it doesn't:
Please Login or Register to view this content.
Any help would be much appreciated.Please Login or Register to view this content.
I worked on this for a solid 20 minutes before the answer did a roundhouse kick out of my computer screen.
You just need to concatenate the wildcards into your criteria.
=SUMPRODUCT((Fruits!$B$2:$B$2000= "*"&Apple&"*" )*(TEXT(Fruits!$A$2:$A$2000,"mmm")=$F$4)*(YEAR(Fruits!$A$2:$A$2000)=$H$4)
This works if you're trying to get all exact and non-exact matches.
Make Mom proud: Add to my reputation if I helped out!
Make the Moderators happy: Mark the Thread as Solved if your question was answered!
Thank you for your time. Your wildcard suggestion works but not for the full formula.
This produces the correct result for all data in the sheet: =COUNTIF(Fruits!$B$2:$B$2000,"*"&M6&"*") OR =COUNTIF(Fruits!$B$2:$B$2000,"*"&"apple"&"*")
However it doesn't work in the sumproduct: =SUMPRODUCT((Fruits!$B$2:$B$2000="*"&"Apple"&"*")*(TEXT(Fruits!$A$2:$A$2000,"mmm")=$F$4)*(YEAR(Fruits!$A$2:$A$2000)=$H$4))
I tried embedding the countif into the sumproduct but this just produced a figure I could not explain.
Hurray!
=SUMPRODUCT(NOT(ISERROR(FIND(LEFT(M5,LEN(M5)-1),Fruits!$B$2:$B$2000)))*(TEXT(Fruits!$A$2:$A$2000,"mmm")=$F$4)*(YEAR(Fruits!$A$2:$A$2000)=$H$4))
This automatically compares the Fruit name without the plural, finds all matches, etc.
Perfect thank you. I would not have got that.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks