SUM of look up values based on multiple criteria

neved89

New Member
Joined
Sep 5, 2017
Messages
2
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):

JAN 2012
JAN 2012
JAN 2012
JAN 2012
JAN 2012
Sales Revenues
SEB Material Costs
SEB Labour Costs
Sales CMII
Sales CMII%
EUR
EUR
EUR
EUR
%
Customer
Contract Type
Customer A
Maintenace
300
10
290
Customer A
Spares
50
40
-90
Customer B
Maintenace
500
500
Customer B
Spares
60
-60
Customer C
Maintenace
1200
70
130
1000
Customer C
Spares
100
50
50
Customer D
Maintenace
900
900

<tbody>
</tbody>

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! :)
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
=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))
 
Upvote 0
=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!
 
Upvote 0
=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.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top