Thanks:  0
Likes:  0

1. Sumif wildcard

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

2. 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!

3. Re: Sumif wildcard help

Originally Posted by Domenic
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.

4. Re: Sumif wildcard help

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

5. 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

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

6. Re: Sumif wildcard help

In that case, try...

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

7. Re: Sumif wildcard help

Originally Posted by Domenic
In that case, try...

Code:
`=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.

8. 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!

9. Re: Sumif wildcard help

Originally Posted by Domenic
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.

10. Re: Sumif wildcard help

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