SUM IF multiple OR criteria across 2 data sets

[hapsheppard]

I am working with the expectation of getting some large data sets in the future and am trying to create a flexible, reusable approach to SUM results if multiple conditions are met.

There are two complications as I understand it:
- I will have multiple criteria (unsure how many)
- The criteria will need to be either a distinct value or permissive (i.e. anything will do)

Abstracted, my data will look something like:
[TABLE="width: 315"]
<tbody>[TR]
[TD="colspan: 5"]Sheet1 - Data set 1:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Amounts to Sum[/TD]
[TD]Subset[/TD]
[TD]Criterion 1[/TD]
[TD]Criterion 2[/TD]
[TD]Criterion 3[/TD]
[TD]Criterion n[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]1[/TD]
[TD]Any[/TD]
[TD]AA[/TD]
[TD]Any[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]1[/TD]
[TD]B[/TD]
[TD]Any[/TD]
[TD]AAA[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]1[/TD]
[TD]C[/TD]
[TD]BB[/TD]
[TD]BBB[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]2[/TD]
[TD]A[/TD]
[TD]Any[/TD]
[TD]Any[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]2[/TD]
[TD]Any[/TD]
[TD]CC[/TD]
[TD]Any[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]3[/TD]
[TD]Any[/TD]
[TD]Any[/TD]
[TD]BBB[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet2 - Data set 2 & calculated values:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Reference[/TD]
[TD]Criterion 1[/TD]
[TD]Criterion 2[/TD]
[TD]Criterion3[/TD]
[TD]Criterion n[/TD]
[TD]Subset 1 (Calc)[/TD]
[TD]Subset 2 (Calc)[/TD]
[TD]Subset 3 (Calc)[/TD]
[/TR]
[TR]
[TD]Ref 1[/TD]
[TD]A[/TD]
[TD]AA[/TD]
[TD]AAA[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]40[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Ref 2[/TD]
[TD]A[/TD]
[TD]BB[/TD]
[TD]AAA[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]40[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Ref 3[/TD]
[TD]A[/TD]
[TD]CC[/TD]
[TD]AAA[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]90[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Ref 4[/TD]
[TD]A[/TD]
[TD]DD[/TD]
[TD]AAA[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]40[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Ref 5[/TD]
[TD]A[/TD]
[TD]DD[/TD]
[TD]AAA[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]40[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
The "(calc)" columns are the calculated one (I did it by hand so apologies for mistakes!)

Basically Data Set 1 is a set of amounts to be summed if certain criteria are met (some of which can be any result). Data Set 2 is a set of instances where certain criteria exist (often repeated and not covering all possibilities) and require the summed values from Data Set 1 for each subset per line.

My searches have shown lots of {=SUM(SUMIFS(...))} style formulae but I can't generalise them to multiple criteria successfully.

My current, failing effort is approximately:
{=SUM(SUMIFS(Amounts to Sum, Sheet1!Subset, "Subset 1 (Calc)", Sheet1!Criterion 1, {Sheet2!Criterion 1,"Any"}, Sheet1!Criterion 2, {Sheet2!Criterion 2, "Any"}, Sheet1!Criterion 3, {Sheet2!Criterion 3, "Any"}))}. This gives me excel errors for each "{Sheet2!Criterion n" segment.
Replacing these cell references with hardcoded data (not an option longterm) also fails. I'm less sure why but I think it's because it would require an n-dimension array rather than a 1D array.

Also, I'm happy to use macros if required.

EDIT: I am using Excel 2010

Thanks for your help / shared confusion in advance.

 

[XOR LX]

Hi.

You are correct when you mention dimensionality: beyond two dimensions, i.e. if you wish to apply arrays of more than one value as OR criteria to more than two criteria_ranges, SUMIF(S)/COUNTIF(S) are no longer viable, and so you must switch to SUMPRODUCT.

So whereas you can legitimately construct something like:

=SUM(SUMIFS(sum_range,criteria_range1,{"criteria1a","criteria1b"},criteria_range2,{"criteria2a";"criteria2b";"criteria2c";"criteria2d"}))

in which, by necessarily transposing one of the array constants such that it is orthogonal to the other (note the semi-colons as separator in one (indicating a column-vector) and commas in the other (row-vector)), we effectively form a two-dimensional (in this case 4-row-by-2-column) array of all 8 combinations of those criteria, if we attempt to add a further set of multiple criteria to another of our criteria_ranges, e.g.:

=SUM(SUMIFS(sum_range,criteria_range1,{"criteria1a","criteria1b"},criteria_range2,{"criteria2a";"criteria2b";"criteria2c";"criteria2d"},criteria_range3,{"criteria3a";"criteria3b";"criteria3c"}))

then, and no matter how we arrange the vector-types of these three arrays, we will never achieve correct results, since the required three-dimensional grid of all possible combinations from these criteria is beyond the means of these functions.

Hence, you would need something like:

=SUMPRODUCT(sum_range,0+(ISNUMBER(MATCH(criteria_range1,{"criteria1a","criteria1b"},0))),0+(ISNUMBER(MATCH(criteria_range2,{"criteria2a","criteria2b","criteria2c","criteria2d"},0))),0+(ISNUMBER(MATCH(criteria_range3,{"criteria3a";"criteria3b";"criteria3c"},0))))

Regards

 

[hapsheppard]

Many thanks XOR LX, your code works great. The only change I made was to use cell references instead of {"criteria1a","criteria1b"} so each line would update based on Data Set 2.

As I couldn't get {CELLREF,"any"} or {CELLREF1,CELLREF2} to work), I inserted dummy columns in Data Set 2 with "Any" repeated in them so as I could use CELLREF1:CELLREF2 as the criteria. If there's a cleaner way great, if not I have working formulae so I'm happy.