Sum based on more than two criteria

[Sajan]

Hi paradise,
Can you elaborate with an example? Use one or two data values to illustrate how colF is to be compared.

 

[paradise]

Hi@Sajan,

Till before your formula was ok.Lets be more specific.If XYZ party buy for e.g Product Apple Minimum 50 cartons then he will get 1 carton free.It means the ratio would be 50:1.But if you buy total 45 carton of Apple during the scheme period then you are not entitled to get,hence your value would be Zero.So,now the condition would be as follows,if Column F(in master sheet)>= sum of sales qty in specific period of respective product ,then it must follow the calculation as sum of sales qty in specific period X Column G ratio.

I have done a little bit workings at Right Hand Side for period one only with certain change in data.The existing formula is not working.Kindly do the calculation in the same worksheet and revert me back.

Thanks again for your consistent work in my post.

With Best Rgds,

 

[Haseeb A]

Paradise,

Use column E in Required tab to get appropriate Scheme. So In E5 enter this Array Formula

=IFERROR(INDEX(SchemeType,MATCH(1,(ProductName=$C5)*(ToDates>=B5)*(FromDates<=B5),0)),"N/A")

Then copy down.

E5:E11 I have given a name as DataScheme

Then in C22 with CTRL+SHIFT+ENTER, then copy down & across.

=IFERROR(IF(SUMIFS(DataSalesQty,DataProduct,$B22,DataScheme,C$20)>1/LOOKUP(2,1/SUMIFS(ColF,ProductName,$B22,ToDates,">="&IF(DataProduct=$B22,DataDates),SchemeType,C$20,FromDates,"<="&IF(DataProduct=$B22,DataDates))),(1/LOOKUP(2,1/SUMIFS(SchemeValue,ProductName,$B22,ToDates,">="&IF(DataProduct=$B22,DataDates),SchemeType,C$20,FromDates,"<="&IF(DataProduct=$B22,DataDates))))/(1/LOOKUP(2,1/SUMIFS(ColF,ProductName,$B22,ToDates,">="&IF(DataProduct=$B22,DataDates),SchemeType,C$20,FromDates,"<="&IF(DataProduct=$B22,DataDates))))*SUMIFS(DataSalesQty,DataProduct,$B22,DataScheme,C$20),0),)

 

[paradise]

Hi Haseeb,

Its not working,pls find enclosed in attachment revised workbook.

However , I have solved this problem in other way.

Thanks for your hard work in trying to solve my queries.

With Best Rgds,

 

[Haseeb A]

Paradise, E5:E11 is an Array Formula. You didn't confirm with CTRL+SHIFT+ENTER.

Use column E in Required tab to get appropriate Scheme. So In E5 enter this Array Formula

 

[paradise]

Thanks.