I'm stumped!! I've tried Sumif, Sumproduct, Offset, Index and I can't seem to figure out to do this. I have a datasheet with multiple columns each with a date heading. I want to add up the data for a particular column based on a reference cell while filtering the sum based on the product type information in another column. For instance if I have a reference cell in sheet 1 (B1) I want the formula to look for that reference in sheet 2 (row 1) to determine, which column to sum. Once the column is found (say column k for instance) I want it to sum up only those numbers that correspond to a specific product type found in (column c). I can get the results using static references but I can't seem to make the sum range dynamic based on a date reference.
Dynamic sumif formula using column and row filtering
- Thread starter barmacost
- Start date
Do make sure that your reference cell and column headers are all true numbers and not text. If so, something like this should work:
=SUMPRODUCT(--(C2:C100=B2),INDEX(D2:Z100,,MATCH(B1,D1:Z1,0)))
In this example, column headers are in D1:Z1, range of data goes down to row 100, B1 is the column header to use, and B2 is the product type to look at.
=SUMPRODUCT(--(C2:C100=B2),INDEX(D2:Z100,,MATCH(B1,D1:Z1,0)))
In this example, column headers are in D1:Z1, range of data goes down to row 100, B1 is the column header to use, and B2 is the product type to look at.