Excel Sumproduct formula with Wild Card

[Mallesh23]

Hi Team,


Need your help , in sumproduct formula, I want Count of Partial Match.
and also how to use not Equal in Partial Match using wild Card.


Below excel formula gives correct result. for Exact Match. Need some change in Third Criteria. highlighted in bold.
=SUMPRODUCT(ISNUMBER(MATCH($A$1:$A$100,$E$2:$E$4,0))*ISNUMBER(MATCH($B$1:$B$100,$F$2:$F$4,0))*ISNUMBER((MATCH($C$1:$C$100,$G$2,0))))




G2 Contains value = Pending
I am trying like this. looking for Total Count which does not Contain Partial Match word *Pending*
*ISNUMBER((MATCH($C$1:$C$100,"*<>Pending*",0))


for Criteria *ISNUMBER((MATCH($C$1:$C$100,$G$2,0))


Plz assist Thanks.


Regards,
mg

 

[James006]

Re: Excel Sumproduct formula with Wild Card Help

Hi,

You could test following

=NOT(ISNUMBER(SEARCH("Pending",C1:C100)))

Hope this will help

 

[njimack]

Re: Excel Sumproduct formula with Wild Card Help

ISERROR(SEARCH("Pending",$C$1:$C$100))

 

[jasonb75]

Re: Excel Sumproduct formula with Wild Card Help

Or you could use the COUNTIFS method that I suggested in your other thread, countifs works with wildcards.

=SUMPRODUCT(COUNTIFS($A$1:$A$100,$E$2:$E$4,$B$1:$B$100,TRANSPOSE($F$2:$F$4),$C$1:$C$100,"<>*"&$G$2&"*"))

 

[Mallesh23]

Re: Excel Sumproduct formula with Wild Card Help

Hi , James,Jason and njiMack , Thank you all for your help your Suggested formula is working.


Need one more help plz.


Below formula works perfectely gives correct result. 4Columns,4 Criteria
=SUMPRODUCT(ISNUMBER(MATCH($H$2:$H$239,$A$2:$A$3,0))_
*ISNUMBER(MATCH($I$2:$I$239,$B$2:$B$3,0))_
*ISNUMBER(MATCH($K$2:$K$239,$C$2:$C$6,0))_
*ISNUMBER(MATCH($J$2:$J$239,{"Finished","Pending"},0)))


In Above formula Column K contains numbers.0-40.
here I want to Count numbers in age buckets - 0-5,6-10,11-15,>15


I am manually putting numbers in a array and getting the result.


*ISNUMBER(MATCH($K$2:$K$239,{1,2,3,4,5},0))
*ISNUMBER(MATCH($K$2:$K$239,{6,7,8,9,10},0))
*ISNUMBER(MATCH($K$2:$K$239,{11,12,13,14,15},0))
*ISERROR(MATCH($K$2:$K$239,{1-15},0))


Can we add these condition in above formula and get the result. <=5, (>6 <=10), (>10 <=15),>15 etc
also Is there any limit in providing multiple Criteria,?




Regards,
mg

 

[jasonb75]

Re: Excel Sumproduct formula with Wild Card Help

also Is there any limit in providing multiple Criteria,?

Not as such, there is a limit on the length of the formula, the number of arguments in a formula and the levels of nesting functions. You have a lot of criteria to add before you will get close to any of the limits.

For age buckets, something like this entered into M2 and filled down to M10 (M2:M9 will show the buckets 0-5,6-10 up to 36-40, M10 will show anything above 40 (numbers that don't fit into the last bucket).

Your criteria means 8 age buckets each covering a range of 5 years (see red bit in the formula).

=INDEX(FREQUENCY($K$2:$K$239,ROW($A$1:$A$8)*5),ROWS($M$2:$M2))