how to count all the qty in sheets and put it in master sheet

You forgot to upload the file.

sorry here is the file
Stocktake28122017WF.xlsx

Hi Ammar,

1. Have the standard format of all your sheets which you gonna give your staff for counting, as attached...
2. Add Sheet Names in P2 until last sheet name.
3. Create a Dynamic Named Range for your sheets with this formula: =OFFSET(MasterSheet!$P$2, 0, 0, COUNT(IF(MasterSheet!$P$2:$P$100="", "", 1)), 1) and name as SheetNames.
4. Add this formula in O2: =IFERROR(SUMPRODUCT(SUMIF(INDIRECT("'"&SheetNames&"'!"&"$C:$C"),$C2,INDIRECT("'"&SheetNames&"'!"&"$E:$E"))),"") and drag down until you need
5. Try entering any count in any sheet for any product list, it will update Master Sheet as requested.

Check in attached...
Ammar_Tahir_Stocktake28122017WF.xlsx

Hi Shums,

thanks i will check and get back to you.

Hi Shum

is the sheetname not auto populated in mastersheet.

Here is another approach with below Array Formula, confirmed with Ctrl+Shift+Enter:
=IFERROR(VLOOKUP($C2,INDIRECT("'"&INDEX(SheetNames,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetNames&"'!$C:$E"),$C2)>0),0))&"'!$C:$E"),3,FALSE),"")
Ammar_Tahir_Stocktake28122017WF_v2.xlsx

You need to create a NamedRange as SheetNames as I directed above

Hi Shum,

There is a bug in formula so i put 1000 units on counted qty for a product see below:

A-BB-01-A      5880064      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      1000      

and when i filter it it shows following:

Location      Product      Des1      Current_Stock      Counted_quantity      Sheet Names
A-BB-01-A      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      29      1000      
A-BB-01-A      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-BB-02-A      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      43      1000      
A-BB-02-A      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-BB-18-F      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-BB-19-B      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-BB-20-E      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-BB-21-B      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-BB-21-E      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-BB-23-C      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-BB-23-D      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-BB-26-D      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-BB-30-E      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-BB-31-E      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-BB-32-B      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-BB-42-D      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-BB-51-D      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-GG-39-E      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      
A-GG-53-F      108318      Worcester Greenstar HE Std     Telescopic Flue 7716 191 082      72      1000      


so 1000 is been entered every where.

Excellent expert, very helpful

You're Welcome Ammar! Glad I was able to help you.