Sum up to 12 columns based on two criteria

[MurrayBarn]

Hi There

I have tried SUMIFS, using AND and SUMIF, SUMPRODUCT etc but can't find a tidy way to sum from 1 to 12 columns based on two criteria. Essentially what I have is a 12 months of Profit and Loss data in columns D:O. I then have the Account Code (criteria 1) in Column A and Department (criteria 2) in column B.

I have a Control sheet that I change the number from 1 to 12 on and I would like a formula that will sum columns D to whatever the control number is, based on criteria 1 and 2. So if I type in 3, it must sum D:F when it finds criteria 1 and criteria 2

I guess I could also use a multiple lookup as there is only one number per month, per account code and per department, but I am not sure how to do that.

Does anybody have a tidy solution?

 

[Ron Coderre]

With
Data tab:

A1: Acct
A2:A20 contains accounts

B1: Dept
B2:B20 contains departments

D1:O1 contains months: Jan, Feb,...Dec
D2:O20 contains amounts

Control tab:
B2: EndMonth...5
B5: Acct
B6: an acct to lookup

C5: Dept
C6: a dept to lookup

D5: YTD
This regular formula returns the sum for that combination for months 1 through the B2 value

D6: =SUM(OFFSET(Data!C1,MATCH(Control!B6&"_"&Control!C6,INDEX(Data!A2:A20&"_"&Data!B2:B20,0),0),1,1,B2))

Note: the OFFSET function is volatile, so it recalcs whenever any cell in the workbook recalcs. Consequently, if too many are used the workbook may become sluggish.

Is that something you can work with?

 

[MurrayBarn]

Thanks Ron

I will try that

 

[MurrayBarn]

Hey Ron - brilliant formula! Works a charm! Thank you

 

[MurrayBarn]

Hi Ron - I have just tried your formula slightly modified. I no longer have a second column of criteria but i have up to four rows that contain the same lookup and it is only summing the first row.

I again have a profit and loss statement for 12 months. In column A is the account code and there can be up to 8 rows with the same account code in it as there are 8 divisions. If I delete the second criteria out of your formula, it correctly looksup and finds the account code, but only sums the first row it finds. Any ideas?