Forum Discussion
Sum of data across multiple columns & rows based on Criteria
Maybe this will explain it a bit better. I have data across multiple rows and columns. Top row has unique criteria (codes/headers, if you will, in numerical format) across all columns. First column has numerical codes that may repeat in several rows. I need the formula to look at all columns and rows, find each instance of a specific code combination, and add up those cells. For example, look for 123 in the top row, and all instances of 567 in the first column, and add all the cells where those intersect.
Yep that is exactly what I guessed and that is exactly what that formula will do. Here is a sample sheet I created with a/b headers and 1/2 on the left column then the formula looks at cells J1 and K1 to decide which combination to sum...
and just to show it really does work here I copied the formula and did all 4 combinations of a/b and 1/2 and then compared the sum of those 4 combinations with the sum of the whole:
both = 2016
So the concept works, the question is why are you saying it doesn't work? If you are getting an error what is the error? Are all the values in the central grid actual values? (i.e. no text or error values) Are you sure you lined up the rows and columns? The original example had A5:HV5 for the header row but the data was only in columns C10:HV1000 so those columns didn't line up (hence in the prior answer I assumed you ment C5:HV5.
