Forum Discussion
Solved
Calculating MODE Across Several Worksheets
Hello. I have data in 11 different cells across several worksheets. For example, I have 20 worksheets in a single workbook and those worksheets all have data in cells D3, D5, D7, D9, D11, D13, D15,...
=MODE(TOCOL(Sheet1:Sheet20!D3))where Sheet1 and Sheet20 are the leftmost and rightmost of the 20 worksheets respectively.
Regards
=MODE(DROP(REDUCE("",TOCOL(SEQUENCE(1,4,21,1)),
LAMBDA(a,x,VSTACK(a,INDIRECT("Sheet"&x&"!D17")))),1))
For the MODE of cell D17 you can apply this formula. The assumption is that the sheetnames are sheet1, sheet2, sheet3 and so on. Within SEQUENCE(1,4,21,1) you can determine the number of worksheets (4) and the worksheet you want to start with (sheet21 or 21 within SEQUENCE).
Then this formula returns the MODE for cell D17 of sheet21, sheet22, sheet23 and sheet24.
Changes can be made dynamically e.g. SEQUENCE(1,20,1,1) would start with sheet1 and return the MODE for sheet1 to sheet20.