Array formula needed to return a sum of range

[dwilliamson1024]

I am in need of a formula (I'm assuming an array which is slightly out of my comfort zone) to return a sum. Essentially I need to return a sum value of multiple columns based on a lookup value, but cannot use VLOOKUP since I want to return the sum of multiple columns.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Item #:[/TD]
[TD]Sum of Jan through March:[/TD]
[/TR]
[TR]
[TD]XYZ[/TD]
[TD]*Formula needed here to return result of 15*[/TD]
[/TR]
</tbody>[/TABLE]




Lookup tab:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]March[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[/TR]
[TR]
[TD]XYZ[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]


It would be nice to also know how to do it as an sum(if(and type array so I could know how to enter a formula that if the item is = XYZ or ABC, give me the sum value of both as I will need that in this file as well.

Thanks for any help!

 

[Marcelo Branco]

Maybe something like this


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][td]

M​

[/td][td]

N​

[/td][td]

O​

[/td][td]

P​

[/td][td]

Q​

[/td][/tr]
[tr][td]

1​

[/td][td]

Item​

[/td][td]

Begin​

[/td][td]

End​

[/td][td]

Result​

[/td][td] [/td][td]

Item​

[/td][td]

Jan​

[/td][td]

Feb​

[/td][td]

March​

[/td][td]

Apr​

[/td][td]

May​

[/td][td]

June​

[/td][td]

July​

[/td][td]

Aug​

[/td][td]

Sep​

[/td][td]

Oct​

[/td][td]

Nov​

[/td][/tr]


[tr][td]

2​

[/td][td]

XYZ​

[/td][td]

Jan​

[/td][td]

March​

[/td][td]

15​

[/td][td] [/td][td]

XYZ​

[/td][td]

5​

[/td][td]

5​

[/td][td]

5​

[/td][td]

5​

[/td][td]

5​

[/td][td]

5​

[/td][td]

5​

[/td][td]

5​

[/td][td]

5​

[/td][td]

5​

[/td][td]

5​

[/td][/tr]


[tr][td]

3​

[/td][td]

ABC​

[/td][td]

March​

[/td][td]

Aug​

[/td][td]

12​

[/td][td] [/td][td]

ABC​

[/td][td]

2​

[/td][td]

2​

[/td][td]

2​

[/td][td]

2​

[/td][td]

2​

[/td][td]

2​

[/td][td]

2​

[/td][td]

2​

[/td][td]

2​

[/td][td]

2​

[/td][td]

2​

[/td][/tr]


[tr][td]

4​

[/td][td]

XYZ​

[/td][td]

Feb​

[/td][td]

Sep​

[/td][td]

40​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[/table]

Formula in D2 copied down
=SUM(INDEX(F:Q,MATCH(A2,F:F,0),MATCH(B2,$F$1:$Q$1,0)):INDEX(F:Q,MATCH(A2,F:F,0),MATCH(C2,$F$1:$Q$1,0)))

Hope this helps

M.

 

[dwilliamson1024]

Thanks Marcelo. That is an interesting angle at it, to include the begin and end date range and use match index (which I am not that familiar with either).
That being said, I can do some cool stuff with this to expand on it. Quick follow up question, does the formatting in columns B/C need to match as a text to the column headers in my subsequent table, such as =text(A1,"mmmm") when cell A1 contains 8/1/2015?

 

[Marcelo Branco]

The formats must match, i.e, if the headers in the table (lookup_array) are text the lookup_value in MATCH function must be text as well. On the other hand, if the headers are real dates the lookup_value must be a date.

M.

 

[dwilliamson1024]

I'm getting a reference error. Any ideas?

My current formula: (which gets ugly by switching between tabs)

Component ROP Review tab: Item number is in column J, start date is in column G, end date is in column H in the text format of MM/YYYY
Forecast tab: column A has the item numbers. Column M-AJ has Jan 2015-Jan 2016 numbers in a table, with the header in row 3 containing the date format in the same manner MM/YYYY

=SUM(INDEX(Forecast!A:AJ,MATCH('Component ROP Review'!J2,Forecast!A:A,0),MATCH('Component ROP Review'!G2,Forecast!$M$3:$AJ$3,0)):INDEX(Forecast!M3:AJ3,MATCH('Component ROP Review'!J2,Forecast!A:A,0),MATCH('Component ROP Review'!H2,Forecast!$M$3:$AJ$3,0)))

 

[Marcelo Branco]

Not sure about your data layout

See if this works

=SUM(INDEX(Forecast!A:AJ,MATCH('Component ROP Review'!J2,Forecast!A:A,0),MATCH('Component ROP Review'!G2,Forecast!$A$3:$AJ$3,0)):INDEX(Forecast!A:AJ,MATCH('Component ROP Review'!J2,Forecast!A:A,0),MATCH('Component ROP Review'!H2,Forecast!$A$3:$AJ$3,0)))

M.

 

[Marcelo Branco]

Or maybe this

=SUM(INDEX(Forecast!M:AJ,MATCH('Component ROP Review'!J2,Forecast!A:A,0),MATCH('Component ROP Review'!G2,Forecast!$M$3:$AJ$3,0)):INDEX(Forecast!M:AJ,MATCH('Component ROP Review'!J2,Forecast!A:A,0),MATCH('Component ROP Review'!H2,Forecast!$M$3:$AJ$3,0)))

M.

 

[dwilliamson1024]

Second one works perfect, thanks Marcelo!!!