SUM of look up values based on multiple criteria

[neved89]

Hello everyone,

I am trying to calculate YTD (year to date) sum of Sales Revenues based on multiple criteria.

Basically the idea is to have two drop down list in Dashboard sheet. The first drop down is used to select Customer Name and the second drop down list is used to choose Reporting Month as you can see below:


-- removed inline image ---


In my database, I have a structure like below which continues until DEC 2012 (and unfortunately I cannot change the database structure):

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]JAN 2012
[/TD]
[TD]JAN 2012
[/TD]
[TD]JAN 2012
[/TD]
[TD]JAN 2012
[/TD]
[TD]JAN 2012
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Sales Revenues
[/TD]
[TD]SEB Material Costs
[/TD]
[TD]SEB Labour Costs
[/TD]
[TD]Sales CMII
[/TD]
[TD]Sales CMII%
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]EUR
[/TD]
[TD]EUR
[/TD]
[TD]EUR
[/TD]
[TD]EUR
[/TD]
[TD]%
[/TD]
[/TR]
[TR]
[TD]Customer
[/TD]
[TD]Contract Type
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer A
[/TD]
[TD]Maintenace
[/TD]
[TD]300
[/TD]
[TD][/TD]
[TD]10
[/TD]
[TD]290
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer A
[/TD]
[TD]Spares
[/TD]
[TD][/TD]
[TD]50
[/TD]
[TD]40
[/TD]
[TD]-90
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer B
[/TD]
[TD]Maintenace
[/TD]
[TD]500
[/TD]
[TD][/TD]
[TD][/TD]
[TD]500
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer B
[/TD]
[TD]Spares
[/TD]
[TD][/TD]
[TD]60
[/TD]
[TD][/TD]
[TD]-60
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer C
[/TD]
[TD]Maintenace
[/TD]
[TD]1200
[/TD]
[TD]70
[/TD]
[TD]130
[/TD]
[TD]1000
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer C
[/TD]
[TD]Spares
[/TD]
[TD]100
[/TD]
[TD]50
[/TD]
[TD][/TD]
[TD]50
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer D
[/TD]
[TD]Maintenace
[/TD]
[TD]900
[/TD]
[TD][/TD]
[TD][/TD]
[TD]900
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

FYI: there are empty cells in the data as shown above.

Lets say for example in Dashboard sheet I select Customer B from the first drop down and MAR 2012 from the second drop down. Then I would like to have a formula to calculate sum of Sales Revenues from JAN 2012 to MAR 2012 for Customer B only for Maintenance contract type and give me the output in the cell below YTD Value.

I tried several formulas but could not make it work. I have an example excel file if needed, but I dont know how to attach it here.


Thank you in advance!

 

[mart37]

=SUMPRODUCT((C1:Q1>=DATE(2012,1,1))*(C1:Q1<=cell with enddate) *(C2:Q2="Sales Revenues")*(A5:A11=cell with customer)*(B5:B11="Maintenace")*(C5:Q11))

 

[neved89]

=SUMPRODUCT((C1:Q1>=DATE(2012,1,1))*(C1:Q1<=cell with enddate) *(C2:Q2="Sales Revenues")*(A5:A11=cell with customer)*(B5:B11="Maintenace")*(C5:Q11))

Thank you for your reply!

Data in the first row is not Date, it is in Text and I cannot change it to Date (data in this format is coming directly from the company servers). Is there any other way to make this work?

Thanks!

 

[mart37]

=SUMPRODUCT((COLUMN(C1:Q1)<=MATCH(LEFT(cell with enddate,3),{"jan";"feb";"mar";"apr"},0 )*5+2) *(C2:Q2="Sales Revenues"))*(A5:A11=cell with customer)*(B5:B11="Maintenace")*(C5:Q11))

For a complete year you must expand the array.