Hmm. I see an Edit on your first post... but that could be because I have Moderator priviliges...

Just reply to this post and add an attachment (paper w/clip icon at top right of reply box)

*That I can do. So I made a few updates in the sample workbook. I want to emphasize that all the formulas I have created and need to create will be in the SUMMARY tab. Here's what my original post would look like had I been able to edit it:*

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 (2 rows and 2 columns) in a single formula? Here's a brief overview of the situation. All my formulas are on the "summary" worksheet.

Variables: [Core.xlsm]Parameters!$A$1:$O$30 <----(This sheet was cleaned for the sample and is not used)

Dataset: [Core.xlsm]Data!$A$1:$BD$100 <----(Source data my formulas reference, this is a minuscule sample population, I don't expect all criteria to produce matches)

Output: [Reports.xlsx]Summary!$A$1:$BD$13 <----(Active worksheet for this request, all variables are pulled from the column and row headings)

Criteria 1: [Reports.xlsx]Summary!C$2 <----(Based on the column header value)

Criteria 2: =RIGHT(C$1,LEN(C$1)-FIND(" ",C$1)) <----(This extracts the number from the title "Phase #")

Criteria 3: [Reports.xlsx]Summary!$A6 <----(based on the primary row header value "Project Name")

Criteria 4: [Reports.xlsx]Summary!$B6 <----(based on the secondary row header value "Region")

Criteria 5: [Reports.xlsx]Summary!$B1 <----(based on the "Key" value applied by the user, if they wish to provide one)

Result:

I can use the SUMIFS function to return matches on any multiples row criteria OR multiple column criteria I wish to include scuh as this formula in Summary!C6:

`=SUMIFS(Data!$R$2:$R$100,Data!$BD$2:$BD$100,C$2,Data!$AT$2:$AT$100,RIGHT(C$1,LEN(C$1)-FIND(" ",C$1)))`

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 in Summary!C3:

`{=SUMPRODUCT((Data!$BD$2:$BD$100=C$2)*(Data!$J$1:$AR$1=$A3),(Data!$J$2:$AR$100))}`

But this breaks down and returns an #N/A error when I try to add in a third criteria, let alone a fourth or fifth (prospective formula for C4 if it worked):

`{=SUMPRODUCT((Data!$BD$2:$BD$100=B$4)*(Data!$A$1:$AR$1=A$4)*(Data!$O$2:$O$100=RIGHT(C1,LEN(C1)-FIND(" ",C1))),(Data!$J$2:$AR$100))}`

I have attached a sanitized sample workbook that is 100 records (from a source set of over 5000). All candidate formulas should appear in Summary!C3:C13 so they can be copied into the columns to the right.