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

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

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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?