How to calculate the mode of values in different sheets

[ijl0322]

Hi everyone!

I have a question about calculating the “mode” of several values in different worksheets. I would like to calculate the mode of every value in “cell A1” across sheet 1~sheet 3, and display the mode in the A1 cell of sheet 4. I’ve tried using this formula =MODE (sheet1: sheet3!A1), but even though it works perfectly when calculating average and median, it always shows #REF! when I try to calculate its mode. Am I doing anything wrong? Please help me as soon as possible, I’m afraid that my boss will kill me if I don’t get this right soon T.T

Thank you so much! I really appreciate your help!

p.s. I have attached a sample
Sample worksheet.xlsx

[Glenn Kennedy]

As set up, it is an array formula and needs to be set by hitting CTRL + SHIFT + ENTER and not just enter to make it work. Do that and you will get your expected answer (1, 2 & 3).

[ijl0322]

Hi Glenn,

Thank you so much for your help!! You are a genius!!

[ijl0322]

Hi everyone,

I was wondering, is it possible to calcualte the mode while excluding the number 1??

Thanks a lot!!!