Using A Cell As An Array Reference In SUMIFS Formula

[Wookiee]

I have a report for which I would like to set up a Quarterly summary section. There are 6 working teams listed in my table and I have created two cells with data validation to allow me to specify which team and for which quarter I would like to pull data. My intent is to have a formula in one cell which can extrapolate the quarterly team information based on the values in the data-validated cells.

My worksheet is set up so that:

• The data table (tblD) contains metrics for all teams for the full year
• Team names appear in Column A of the table and the reporting month appears in Column B
• Cell M10 has data validation so that the only choices are: Q1, Q2, Q3, or Q4
• Cells X1:X4 contain the quarterly values mentioned above
• Cells Y1:Y4 contain array references, so that Cell X1 = Q1 and Cell Y1 = {"January", "February", "March"}
• Cell N10 contains a VLOOKUP which returns the appropriate array from Column Y based on value in Cell M10
• Cell L11 has data validation so that the only choices are the 6 team names

Now I was able to get quarterly information by manually typing the 3-month array into my formula like so:

=SUM(SUMIFS(tblD[TU5],tblD[Team],$L$11,tblD[Month],{"January","February","March"}))

BUT! When I try to use a formula which points to the VLOOKUP cell (N10)--thus allowing me to make the display dynamic--the result is always 0.

=SUM(SUMIFS(tblD[TU5],tblD[Team],$L$11,tblD[Month],$N$10))

While inspecting the formula in the formula bar, I noticed that the values listed in the array appeared with double quotes (i.e. "{""January"",""February"",""March""}"). I tried every permutation of typing the array in the lookup cells I could think of, but none would allow my formula to properly calculate the total I needed.

{"January", "February", "March"}
{January, February, March}
January, February, March

Although I don't often utilize arrays in my formulas, I do employ them from time to time. I'm hoping there's some factor I might have overlooked which keeps my formula from working. Can someone please help tell me what I need to do in order to fix this formula, or is there an issue preventing array references stored in cells from being used in formulæ?

I apologize for not attaching a file. I'm at work and don't have the ability to visit file-storage sites (and my source data is linked to other files on our network, to boot). I'll be happy to provide clarification or additional information if needed.

Thanks y'all!

 

[jdigital]

Oh i just realized that i didn't take my sum back out on your sumproduct and yours didn't need to be an array formula. I like that better I think. Thanks again!

 

[Aladin Akyurek]

Oh i just realized that i didn't take my sum back out on your sumproduct and yours didn't need to be an array formula. I like that better I think. Thanks again!

You are welcome.