sum based on more than 2 lookup for calculation of scheme qantity based on sales quantity

Dear All forum contributor,

Let me explain my problem.My company has different kinds of schemes on per unit of sales on different kinds of products on various dates.Now the problem is that,I have a data in which there is date,product and sales quantity in 'Required' sheet from B4:D9.I want to have a sum of sales quantity of different kinds of products on each scheme criteria date range(B:E) X per unit of scheme(from 'master' sheet in column G).I have created another sheet named as 'master' in which I have set for e.g 4 kinds (shown in column 'C' defined as A,B,C,D)of scheme of different dates(Period is defined as start date as 'From' and end date as 'To' with different kinds of product.

Hence,you have to look following this :
1.Data range from 'master' sheet
2.Sum of all the product value based on different kinds of date range
3.Determine scheme qty of each product from master sheet of 'G' column.
4.Multiply sn.2 x 3

OR,you may solve as you like

Kindly,help me in resolving the issue of this in my 'Required' sheet.

With Best Rgds,
suresh

Dear sir,

Thanks for your prompt reply.I am verifying the result & soon will reply back to u.

suresh

Originally Posted by tigeravatar

paradise2sr,

Using your provided example file, use this formula in sheet 'Required' cell C20 then copy over and down:

Please Login or Register  to view this content.

Dear Sir,

Now I have checked but you have mistaken & has not taken data from 'Required' sheet from D5:D9 for total sales qty with corresponding product name in C5:C9 ,lookup date from B5:B9.All these things you have to look from 'master' sheet.For your simplicity I have assign A,B,C,D in 'master ' sheet,so that for each product if this is identified that one could easily identify column G data of 'master' sheet and then sum up data of respective product from 'Required' sheet from D5:D9 and these two data will be multiplied to get the result.
The table in 'Required' sheet at B18:F23.

I have deleted the column data that was making a problem in 'master' sheet of column F with slight changes in data in column G as same value was all over.

Revised excel sheet is enclosed.

I will be eagerly waiting for your reply.

With Best Rgds,
Suresh

Dear Sir,

That's really worked.I am very much thankful to you.How would you revise your formula in order to get the same result if column C of master sheet & row 18 of 'Required' sheet data is removed.

Also now I have kept data in F column of 'master' sheet.This is another condition which is being taken into consideration.Now this column F under 'master' sheet says that if the sales of D column of 'Required' sheet is greater or equal of respective product in respective date range as specified in column F condition in 'master' sheet then give scheme of column G of 'master'sheet otherwise don't give any scheme of column G i.e give zero.

With Best Rgds,
Suresh

Encl: Scheme_revised_1.xlsx

Dear All,

Still waiting for the reply

With Best Rgds,
Suresh