SUM(SUMIFS( with multiple criteria, one range, and wildcards

[scholl43]

I've searched a lot of forums and videos to try to find an answer for this, but can't seem to make any of those solutions work. I have columns B,D,E, and F with relevant data.

Column B is an admin code that I'll use for the range (SUM) or criteria range (SUMIFS) and I'll use column D for the sum_range. With the right absolute addressing, I'll be able to drag this to the right and have columns E and F automatically update.

Unfortunately the admin codes in column B are not uniform. They're in two separate categories, which I want to roll up based on the three columns (a 2X3 table will result). For the criteria for one category, I want to use cell references within ranges. So if H33 = 1301 and H34=1327, I can say =SUMIFS(D4:D45,$B$4:$B$45,">="&H33,$B$4:$B$45,"<="&H34)

In addition to that, some numbers are of varying lengths, but I want all that start with 1301 (for example). Where J33=1301, I tried saying =SUMIF($B$4:$B$54,J34&"*",D4:D54), but that returns a value of zero. I'm not sure what's broken down here. Ultimately, I thought I could work this into a =SUM(SUMIFS(...)) statement, but the wildcards and references don't seem to be playing along.

On some older posts I found that some people do the following to combine SUMIF and OR statements:

=SUM(SUMIFS(C1:C10,A1:A10,E5,B1:B10,{"labor","fringe","merit"}))

When I tried something similar, the curved brackets prevent me from using cell references. It seems like I should be able to put =SUM(SUMIFS($D$4:$D$54,{cell references and cell refs&"*"},$B$4:$B$54)), but I'm not able. Overall I'm trying to get Excel to sum values in columns D,E,and F based all values that meet the criteria in a range (great than/less than), cell references (unique values), and strings of numbers combining cell references and wildcards.

Can anyone help?

 

[barry houdini]

Hello scholl43, welcome to MrExcel

That's several questions in one there and I might not answer all of them but to start you off, in a formula like this

=SUM(SUMIFS(C1:C10,A1:A10,E5,B1:B10,{"labor","fringe","merit"}))

if you want the {"labor","fringe","merit"} part to be derived from cell references you could use CHOOSE like this

=SUM(SUMIFS(C1:C10,A1:A10,E5,B1:B10,CHOOSE({1,2,3},Z3,Y4,X2)))

where {1,2,3} will change depending on the number of cell references - you can add a wildcard to each value in the array like this:

=SUM(SUMIFS(C1:C10,A1:A10,E5,B1:B10,CHOOSE({1,2,3},Z3,Y4,X2)&"*"))

....but wildcards don't work in SUMIF/SUMIFS with numeric values, the source data would need to be text-formatted......or you will need to use two conditions like ">=13010" and "<=13019"....although that will only work for 5 digit numbers