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!

 

[KrisSquare]

Currently you have the month values in single cell, Y1. change it to a way where Y1=January, Y2=February and Y3=March (No quotes)

Now try this formula

[SIZE=2][COLOR=#006400][B]=SUM(SUMIFS(tblD[TU5],tblD[Team],$L$11,tblD[Month],indirect("R1C25:R3C25",0)))
[/B][/COLOR][/SIZE]
or

[B]=SUM(SUMIFS(tblD[TU5],tblD[Team],$L$11,tblD[Month],$Y$1:$Y$3))[/B]

Press Ctrl+Shift+Enter to Make it array formula.
R1C25:R3C25 indicates Absolute reference to $Y$1:$Y$3. Modify it according to your logic to get desired results

 

[KrisSquare]

Ignore my previous post as it directly refers to Column Y
Try this
Currently you have the month values in single cell, Y1. change it to a way where Y1=January, Y2=February and Y3=March (No quotes)

Write 3 vlookups to get all three values into seperate cells. Example N10, O10 and P10 would have Jan, Feb and Mar

Now try this formula

Code:

[SIZE=2][COLOR=#006400][B]
=SUM(SUMIFS(tblD[TU5],tblD[Team],$L$11,tblD[Month],indirect("R10C14:R10C16",0)))
[/B][/COLOR][/SIZE]
or
[B]
=SUM(SUMIFS(tblD[TU5],tblD[Team],$L$11,tblD[Month],$N$10:$P$10))
[/B]

Press Ctrl+Shift+Enter to Make it array formula.

 

[Aladin Akyurek]

Add the following code to your wb using Alt+F11...

Public Function EVAL(theInput As Variant) As Variant
'
' if UDF evaluate the input string as though it was on this sheet
' else evaluate for activesheet
'
Dim vEval As Variant
Application.Volatile
On Error GoTo funcfail
If Not IsEmpty(theInput) Then
If TypeOf Application.Caller.Parent Is Worksheet Then
vEval = Application.Caller.Parent.Evaluate(CStr(theInput))
Else
vEval = Application.Evaluate(CStr(theInput))
End If
If IsError(vEval) Then
EVAL = CVErr(xlErrValue)
Else
EVAL = vEval
End If
End If
Exit Function
funcfail:
EVAL = CVErr(xlErrNA)
End Function

Now you can invoke:


=SUM(SUMIFS(tblD[TU5],tblD[Team],$L$11,tblD[Month],EVAL($N$10)))</pre>

 

[Wookiee]

Thank you, KrisSquare! I really appreciate your assistance.

I used a slight variation on your suggestion just because of the way I had my data structured in the worksheet, but the formula worked very well once I got it all sorted out.

Have a great day (or middle of the night, as I suspect it is where you live)!

 

[Wookiee]

That's a great function, Aladin. Many thanks for sharing it!

 

[Aladin Akyurek]

That's a great function, Aladin. Many thanks for sharing it!

You are welcome.

 

[jdigital]

This is exactly what I'm trying to do. I tried your EVAL function, but it's still just returning the match from the first of the array. I have the below where the withdrawn from contains the name of another sheet and keywords to match are on the same page...(my excel knowledge is very limited!). I can swap the two keywords around in the array and get the second number i want, but it's not doing a sum on both numbers.

=ABS(SUMIFS(INDIRECT("'"&[@[Withdrawn From]]&"'!C:C"),INDIRECT("'"&[@[Withdrawn From]]&"'!E:E"),EVAL([@Keywords])))

With keywords containing...

{"Cardtronics*","CT 7-11*"}

 

[Aladin Akyurek]

Try:

=SUMPRODUCT(ABS(SUMIFS(INDIRECT("'"&[@[Withdrawn From]]&"'!C:C"),INDIRECT("'"&[@[Withdrawn From]]&"'!E:E"),EVAL([@Keywords]))))

 

[jdigital]

Yeah I added sum to it and pressed ctrl, shift enter and it started working...your sumproduct also worked if i did that. Thanks Aladin!