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:

Sheet1 - Data set 1: Amounts to Sum Subset Criterion 1 Criterion 2 Criterion 3 Criterion n 10 1 Any AA Any 20 1 B Any AAA 30 1 C BB BBB 40 2 A Any Any 50 2 Any CC Any 60 3 Any Any BBB <tbody> </tbody> Sheet2 - Data set 2 & calculated values: Reference Criterion 1 Criterion 2 Criterion3 Criterion n Subset 1 (Calc) Subset 2 (Calc) Subset 3 (Calc) Ref 1 A AA AAA 10 40 0 Ref 2 A BB AAA 0 40 0 Ref 3 A CC AAA 0 90 0 Ref 4 A DD AAA 0 40 0 Ref 5 A DD AAA 0 40 0 <tbody> </tbody>

<tbody>
</tbody>

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.