Sumif wildcard

[Renevatia]

Hi, new here. Been scratching my head around this for while.

I am currently using a =sum(sumif(A1:A10, {"*text1*","*text2*","*text3*"}, B1:B10)) formula to replace a really messy =sum(vlookup^n)
=sumif(A1:A10, C1, B1:B10) would work with a reference but I cannot throw in multiple references.
I am wondering if there is any way I can use cell reference for the criteria array for the sumif function. Everything I've tried so far seems to throw syntax error. Only thing that works is explicit text.
Since array is throwing wildcard kind of like a query is there a way for me to toss an OR statement in there to concatenate the entire criteria?

Are there any syntax that would support the following structures:

=sumif(A1:A10, C1, B1:B10)
where C1.text = *text1*|*text2*|*text3*

or

=sum(sumif(A1:A10, {C1, C2, C3}, B1:B10))
where C#.text = *text#*

I know I could simply hard code the criteria in or use multiple sumif or sumifs with cell reference, but that may still end up too messy and I would not be able to use dynamic range / range sizes for this method.
The only other solution I can think of is to bandage this with is a macro to populate the formulas.

plssendhelp

 

[Domenic]

Re: Sumif wildcard help

If each cell value includes the wildcard characters (ie. *text1*), try...

=SUMPRODUCT(SUMIF(A1:A10,C1:C3,B1:B10))

If each cell value doesn't include the wildcard characters (ie. text1), try...

=SUMPRODUCT(SUMIF(A1:A10,"*"&C1:C3&"*",B1:B10))

Hope this helps!

 

[Renevatia]

Re: Sumif wildcard help

If each cell value includes the wildcard characters (ie. *text1*), try...

=SUMPRODUCT(SUMIF(A1:A10,C1:C3,B1:B10))

If each cell value doesn't include the wildcard characters (ie. text1), try...

=SUMPRODUCT(SUMIF(A1:A10,"*"&C1:C3&"*",B1:B10))

Hope this helps!

Thank you, I didn't realize sumproduct can be used like that.

However, I realized an inherent problem with the sum and sumif. Unions in those sets are counted twice so when I passed in "*" as one of the sumif it gave me a number greater than the sum of all return column.
This means even explicit declaration of the array like {"*text1*","*text2*","*text3*"} wouldn't work since it works like a loop of sumif instead of just a single query of multiple OR statements.

 

[Domenic]

Re: Sumif wildcard help

Can you post a small sample of the data, the criteria involved, and the expected result?

 

[Renevatia]

Re: Sumif wildcard help

[TABLE="width: 820"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Sub-Category[/TD]
[TD]Product Name[/TD]
[TD]Sales[/TD]
[TD]Quantity[/TD]
[TD]Discount[/TD]
[TD]Profit[/TD]
[/TR]
[TR]
[TD]Bookcases[/TD]
[TD]Bush Somerset Collection Bookcase[/TD]
[TD="align: right"]261.96[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]41.9136[/TD]
[/TR]
[TR]
[TD]Chairs[/TD]
[TD]Hon Deluxe Fabric Upholstered Stacking Chairs, Rounded Back[/TD]
[TD="align: right"]731.94[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]219.582[/TD]
[/TR]
[TR]
[TD]Labels[/TD]
[TD]Self-Adhesive Address Labels for Typewriters by Universal[/TD]
[TD="align: right"]14.62[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6.8714[/TD]
[/TR]
[TR]
[TD]Tables[/TD]
[TD]Bretford CR4500 Series Slim Rectangular Table[/TD]
[TD="align: right"]957.5775[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0.45[/TD]
[TD="align: right"]-383.031[/TD]
[/TR]
[TR]
[TD]Storage[/TD]
[TD]Eldon Fold 'N Roll Cart System[/TD]
[TD="align: right"]22.368[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]2.5164[/TD]
[/TR]
[TR]
[TD]Furnishings[/TD]
[TD]Eldon Expressions Wood and Plastic Desk Accessories, Cherry Wood[/TD]
[TD="align: right"]48.86[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]14.1694[/TD]
[/TR]
[TR]
[TD]Art[/TD]
[TD]Newell 322[/TD]
[TD="align: right"]7.28[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1.9656[/TD]
[/TR]
[TR]
[TD]Phones[/TD]
[TD]Mitel 5320 IP Phone VoIP phone[/TD]
[TD="align: right"]907.152[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]90.7152[/TD]
[/TR]
[TR]
[TD]Binders[/TD]
[TD]DXL Angle-View Binders with Locking Rings by Samsill[/TD]
[TD="align: right"]18.504[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]5.7825[/TD]
[/TR]
[TR]
[TD]Appliances[/TD]
[TD]Belkin F5C206VTEL 6 Outlet Surge[/TD]
[TD="align: right"]114.9[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]34.47[/TD]
[/TR]
[TR]
[TD]Tables[/TD]
[TD]Chromcraft Rectangular Conference Tables[/TD]
[TD="align: right"]1706.184[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]85.3092[/TD]
[/TR]
[TR]
[TD]Phones[/TD]
[TD]Konftel 250 Conference phone - Charcoal black[/TD]
[TD="align: right"]911.424[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]68.3568[/TD]
[/TR]
[TR]
[TD]Paper[/TD]
[TD]Xerox 1967[/TD]
[TD="align: right"]15.552[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]5.4432[/TD]
[/TR]
</tbody>[/TABLE]


Sum of total Column("Quantity") = 55

___________________________________________________________________________________________________
Where H1:H4 = {*one*, *irs*, *, *}

Desired result:
1. MagicSumIf(A:A, "*one*|*irs*", D:D) = 13
2. MagicSumIf(A:A, "*one*|*irs*|*", D:D) = 55
or
3. MagicSumIf(A:A, H1:H3, D:D) = 55
4. MagicSumIf(A:A, H1:H4, D:D) = 55

Current result:
1. Sum(SumIf(A:A, H1:H2, D:D) = 13
2. Sum(SumIf(A:A, H1:H3, D:D) = 68
3. Sum(SumIf(A:A, H1:H4, D:D) = 123

 

[Domenic]

Re: Sumif wildcard help

In that case, try...

=IF(ISNUMBER(MATCH("~*",H1:H4,0)),SUM(D:D),SUMPRODUCT(SUMIF(A:A,H1:H4,D:D)))

 

[Renevatia]

Re: Sumif wildcard help

In that case, try...

=IF(ISNUMBER(MATCH("~*",H1:H4,0)),SUM(D:D),SUMPRODUCT(SUMIF(A:A,H1:H4,D:D)))

This is returning double counted entries as long as the sum is not greater than the total sum of column
Lookup with "Art" and "*rt would return the "Art" Row return value of 4 twice when it should only picked up once if it was a query with "Art"|"*rt" equivalent syntax.

 

[Domenic]

Re: Sumif wildcard help

In that case, try...

=SUM(IF(MMULT(IF(ISNUMBER(SEARCH(TRANSPOSE(H1:H4),A2:A14)),1,0),ROW(H1:H4)^0)>0,D2:D14))

...confirmed with CONTROL+SHIFT+ENTER. Although, if the criteria range (ie. H1:H4) can contain empty cells, try the following instead...

=SUM(IF(MMULT(IF(LEN(TRANSPOSE(H1:H4))>0,IF(ISNUMBER(SEARCH(TRANSPOSE(H1:H4),A2:A14)),1,0),0),ROW(H1:H4)^0)>0,D2:D14))

...which also needs to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Renevatia]

Re: Sumif wildcard help

In that case, try...

=SUM(IF(MMULT(IF(ISNUMBER(SEARCH(TRANSPOSE(H1:H4),A2:A14)),1,0),ROW(H1:H4)^0)>0,D2:D14))

...confirmed with CONTROL+SHIFT+ENTER. Although, if the criteria range (ie. H1:H4) can contain empty cells, try the following instead...

=SUM(IF(MMULT(IF(LEN(TRANSPOSE(H1:H4))>0,IF(ISNUMBER(SEARCH(TRANSPOSE(H1:H4),A2:A14)),1,0),0),ROW(H1:H4)^0)>0,D2:D14))

...which also needs to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

That is a really nice way of using arrays!
This works for my current application, but the formula is evaluating all lookup values as *text* since search function finds any match instead of exact match with wildcard.

Is there a way that I can use arrays as toggle but also take advange of wildcard lookup such as the ones in Sumif criteria. Match only throws back the first occurance of each lookup value unfortunately.

 

[Domenic]

Re: Sumif wildcard help

Can you please elaborate, and include some sample data along with the expected result?