SUMIF Formula where the range is a column from an array

Hello, I'm having trouble trying to set up a SUMIF formula where I can set what range it is looking up out of an array by changing one cell. For example. My array is setup where one of the column Labels are Week ending dates. I want to set a cell that I can change the date in and the formula will set the range to the column from the array in the SUMIF formula to whatever date I choose in that cell. I tried to use a sumproduct formula in the range section of a SUMIF statement but it came up with an error. Any help would be appreciated. Thanks!

See the data set attached. A sample array of Data is in A3:L24. This is how I have the data and formulas set up now. I'm trying a count of everyone with a entry above 0 for a certain period of time in the Base columns by department. The end result being in the table on the right in columns P and Q. The goal is to change the SUMIF formula in column Q so that if I change the date in Cell Q1 it will use the range from the appropriate column in the table. Thanks

Originally Posted by protonLeah

1. Why do you have four columns for the department numbers?
2. For 7/7 you have columns for "Base" & "Extra"; for 7/14, you have columns for "Extra","Bonus". For 7/21 you have "Base", Extra","Base"
3. What does column N represent?

Please Login or Register  to view this content.

Modified worksheet with one column for dept and columns for base, extra & bonus. Note that "Base" is first so that the MATCH will find that column first for any date.

I can't modify the columns, its not a data set I have much control over. The dates are there four times because its the four different categories in each week. Its how the data comes out. The only ones I care about right now are the ones marked base. Column is the sum I need to total. I just want a way to have that grid populate changing the cell in Q1 instead of moving the range reference column each week.

Had a typo in the column labels. Attached is an updated file. Thanks!

Thank you!