hi everyone,


in test1 folder "Op List FEB-23 TEST 3.xlsx" is the main file. its data comes from the data folder files. test folder is in desktop location.


So here is the situation, i made the "Op List FEB-23 TEST 3" file to collect data from data folder according to date of files and sheet. "Op List FEB-23 TEST 3" sheet-01-02-23 data file 01-02-23-BB.xlsx, sheet-02-02-23 data file 02-02-23-BB.xlsx so far it works.


In cell#F7 it collected data from "01-02-23-BB" A1 M/C cell#O9. as you can see it has 2 different production data 195.5 & 100. so in cell#F7 i want sum this number since this two number is from same M/C. i tried didnt work since its in same column number. how do i sum it?


2nd in cell#F18 for M/C a12 there is 0 production. but in "01-02-23-BB" there is 244 kg production. why is it showing 0?


There the quite a few situations like this in this.

Attached Files

test1.zip (1.40 MB, 2 views)

=SUMIFS('[01-02-23-BB.xlsx]DELEV-B'!$O$9:$O$568,'[01-02-23-BB.xlsx]DELEV-B'!$C$9:$C$568"A 1")

@xor
this function seems wrong i at last. still error.

Attached Images

ERROR.jpg (100.3 KB, 16 views)

If I open 01-02-23-BB first it works fine for me (with comma as you mention).

i have to change M/C name manualy for every M/C for each sheet (158 times). there are 30 sheets. i have solved this by changing "M/C name" to cell #.

but i have to 1st open data files and work? there are 58-62 files originally in there. how do i work around that?

Structure your data better. Your structure is not suitable for efficient formula building.
For example use database format that is no empty columns, no empty rows such as row 46, 81 etc. in 01-02-23-BB, no totals such as 45, 80, header rows such as 47, 82 etc. Then you might be able to use SUMPRODUCT instead of SUMIFS and so not required to open the data files. You might also want to take a look at Power Query.

that cant be done. as i mentioned in my 1st post "IT CANNOT BE CHANGED". it was build by a only computer literate person available in company back at 2008. its in print oriented format for official use. so that even idiots can understand basic office workflow. then at 2014 they implemented a shitty ERP. if the F**K** ERP did its job i wouldnt be in this much trouble. even IT dept runs by ass+holes.

I am afraid I can't help then.