Equation to calculate the last balance of a commodity

[Hany ali]

hello every one
please i want to correct this formula ,to calculate the last balance for Certain Items ,if this item Duplicate (More than One)as you see in Photo and File

=IF(COUNTIF($C$2:$C2,$C2)>1,LOOKUP(2,1/((Store!$A$2:$A$28=C2)),$F$2:$F$1400),SUMIF(Store!$A:$A,$C2,Store!$B:$B))

 

[Nebu]

Hi:

Not completely sure what you want here... refer to the yellow column in that attached.

Note:

- Array formula , execute by pressing Control+Shift+Enter
- I had constructed the formula in a different column, since you have circular references in your formulas, you may want to rethink the logic how to avoid this.

Thanks

 

[Hany ali]

thanks alot ,but this formula Not which i want-because
if this Goods First Item in The Column
,it Should Be to Take Number Of Balane
From Store Sheet it will be 150 not 70

and you can see the Result Which I want in my File

Goods	Balance	the Result Which I want
Steal	150	150
Leather	80	80
Textile	6	7
Steal	140	140
Textile	6	5
Plastic		85
Copper		60
Textile		4
paper		4
Aluminum		128
paper		3
Leather		60
Plastic		53
Copper		44
Aluminum		99

 

[NARAYANK991]

Hi ,

See if this works :

=IF(COUNTIF(C$2:C2,C2)=1,INDEX(Store!$B$2:$B$9,MATCH(C2,Store!$A$2:$A$9,0)),INDEX(Store!$B$2:$B$9,MATCH(C2,Store!$A$2:$A$9,0)) - SUMIF(C$1:C1,C2, F$1:F1))

Narayan

 

[bosco_yip]

Or..............

=IF(C2="","",VLOOKUP(C2,Store!A:B,2,0)-SUMIF(C$1:C1,C2,F$1:F1))

Regards
Bosco

 

[Hany ali]

thanks alot mr
NARAYANK991
thanks alot mr
bosco_yip
Of course all of the answers and solutions are excellent and this is actually required