# Excel Sumproduct formula with Wild Card

#### Mallesh23

##### Board Regular
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

Last edited:

#### James006

##### Well-known Member
Re: Excel Sumproduct formula with Wild Card Help

Hi,

You could test following

Code:
``=NOT(ISNUMBER(SEARCH("Pending",C1:C100)))``
Hope this will help

#### njimack

##### Well-known Member
Re: Excel Sumproduct formula with Wild Card Help

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

#### jasonb75

##### Well-known Member
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&"*"))

Last edited:

#### Mallesh23

##### Board Regular
Re: Excel Sumproduct formula with Wild Card Help

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

##### Well-known Member
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))