hapsheppard
New Member
- Joined
- Nov 4, 2015
- Messages
- 2
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:
<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.
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:
<tbody> </tbody> Sheet2 - Data set 2 & calculated values:
<tbody> </tbody> |
<tbody>
</tbody>
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.