Sumproduct Formula problem

[sgmpatnaik]

@Hi


Good Afternoon All


In my workbook i am trying to sum the 3 criteria items using with SUMPRODUCT formula so i am getting up to 2 criteria but i am fail to get the third criteria


my work as like


1. Date


2. Brand


3. Shortage


4. Quantity


i want to get the result for the brand wise shortage Quantity in the particular month. For that i used the given below formula but i got the result as #VALUE


=SUMPRODUCT(('Receive Register'!$B$6:$B$5005>=DATE(YEAR($G$5),MONTH($G$5),1))*('Receive Register'!$B$6:$B$5005<=$G$5)*('Receive Register'!$J$6:$J$5005=$A26)*('Receive Register'!$M$6:$M$5005)*'Receive Register'!$N$6:$N$5005,"Shortage")


Range Details of above formula


1. Column B = Date


2. Column J = Brand


3. Column M = Quantity


4. Column N = Shortage


Kindly suggest where is my mistake


Thanking You


Regards


SP

 

[NARAYANK991]

Hi Patnaik ,


I think the problem is here :


'Receive Register'!$N$6:$N$5005,"Shortage"


which should be :


'Receive Register'!$N$6:$N$5005="Shortage"


Generally , it is good practice to put all the logical conditions together , followed at the end by the sum range ; in your case , your formula would be :


=SUMPRODUCT((('Receive Register'!$B$6:$B$5005>=DATE(YEAR($G$5),MONTH($G$5),1))*('Receive Register'!$B$6:$B$5005<=$G$5)*('Receive Register'!$J$6:$J$5005=$A26)*('Receive Register'!$N$6:$N$5005="Shortage"))*('Receive Register'!$M$6:$M$5005))


Narayan

 

[sgmpatnaik]

@Narayank


Hi


Thanks it's working fine, Just miss understood the problem or confused .


Thanks for your Guidelines about the formula


Regards


SP