My question is this: Is it possible to add the values in a large dataset based on multiple row and multiple column criteria? Does anyone dare face the maw of madness and explain how to have 3 or 4 criteria (1-2 rows and 2 columns) in a single formula? I am confident it can be done, but I have been at it for hours and I hit a wall. Any working sample would be welcome. I've not had any luck finding something quite like this online. Here's a brief overview of the situation. All my formulas are on the "output" workbook listed below. [INDENT]
Variables: [Core.xlsm]Parameters!$A$1:$Z$100
Dataset: [Core.xlsm]Data!$A$16:$AR$5320
Output: [Reports.xlsx]Summary!$A$20:$L$50
Row Criteria 1: [Reports.xlsx]Summary!B$22
Row Criteria 2: =RIGHT(B$21,LEN(B21)-FIND(" ",B21))
Column Criteria 1: [Reports.xlsx]Summary!$A23
Column Criteria 2: <User Selected Reference or Value>[/INDENT]
Result:
I can use the SUMIFS function to return matches on any multiples row criteria OR multiple column criteria I wish to include:
=SUMIFS([Core.xlsm]Data!$J$16:$K$5320,[Core.xlsm]Data!$BD$16:$BD$5320,D$22,[Core.xlsm]Data!$O$16:$O$5320,RIGHT(B$21,LEN(B21)-FIND(" ",B21)))
I can process any combination of one row and one column criteria to return a conditional sum on a single column of values using this formula:
{=SUMPRODUCT(([Core.xlsm]Data!$BD$16:$BD$5320=B$22)*([Core.xlsm]Data!$J$15:$AR$15=$A23),([Core.xlsm]Data!$J$16:$AR$5320))}
But this breaks down and returns an #N/A error when I try to add in a third criteria, let alone a fourth:
{=SUMPRODUCT(([Core.xlsm]Data!$BD$16:$BD$5320=B$22)*([Core.xlsm]Data!$J$15:$AR$15=$A23)*([Core.xlsm]Data!$O$15:$O$5320=RIGHT(B21,LEN(B21)-FIND(" ",B21))),([Core.xlsm]Data!$J$16:$AR$5320))}
My dataset has unique column names but the data itself is mostly calculations based on Indirect and Index Match references to the detail worksheets where the values of each line item in the dataset sheet are calculated based on their unique values combined with the parameters from the variables sheet. There are thousands of sheets, one for each row in the dataset. Yes the workbook is huge. Yes I'd rather use SQL, but it is not an option for this project.
I have attached a sample workbook but in the process of whittling it down to a small enough dataset, I realize that the formulas I listed here may not match exactly anymore. Thanks for perusing my dilemma.