Can I make the mode formula in numbers ignore blanks or zeros?

Question

Level 1

4 points

Can I make the mode formula in numbers ignore blanks or zeros?

I have a spreadsheet with a dozen cells that uses sumifs to count instances which meet some regex criteria from another sheet.

I have another cell which uses the mode function to return the mode of those dozen cells.

The problem I have is some of those sumifs'd cells will be blank. They were often zero but I used custom formatting to replace zeros with nothing as per the advice I found on this community. One step forward, one step back.

I've tried putting an if condition in the sumifs formula to return "" if the result is 0 but the mode function errors as the data type is a string.

I've tried putting the same if statement into the mode function directly but it errors too as the if has to return true or false which also isn't a numeric data type, which mode needs.

To better visualise my setup, imagine a sheet with a years worth of bank account transactions. Then another sheet where I want to show the mode day that a specific transaction was made for each month. So one column for each month.

My problem is early in the year there is no data for later in the year (as in, in the future) therefore the mode function returns blank (or zero before the custom formatting).

I need a way to ignore blanks or zeros.

Thank you if you could be bothered to read the above boring essay. Thanks again if you have any insight toward a solution/workaround.

p.s. I'm still very much a novice so apologies if any of that terminology was wrong.

MacBook Pro 13″, macOS 12.7

Posted on Jun 12, 2025 2:41 AM

Reply

Answer 1

Top-ranking reply

SGIII

Level 8

37,532 points

Jun 12, 2025 4:08 AM in response to jpurk

I'm having trouble visualizing the problem you are seeing. A picture (screenshot) is often worth a 1000 words (essay).

Here's what I have:

MODE seems to happily ignore blank cells.

(The errors in E and F occur because there are no repeated values in those columns. )

It will count 0s though. You can filter those 0s out if you want.

Here in the Footer Row cell of column C:

=MODE(FILTER(C,C<>0))

MODE - Apple Support

FILTER - Apple Support

SG

Reply

Answer 2

Badunit

Level 7

30,342 points

Jun 13, 2025 11:11 AM in response to jpurk

No, they are the same I think. The difference is I have been using MODE(B) or MODE(B1:B4) where column B is a mix of numbers and text. If I do MODE(B1,B2,B3,B4) and one of those is text then I get the error you get. If you rewrite yours to be something like MODE(B2:E2) instead of four separate cells you should not get the error.

Reply

Answer 3

jpurk Author

Level 1

4 points

Jun 17, 2025 5:26 AM in response to Badunit

That 100% solved my problem. I've pushed the columns with the sumifs to the end of the sheet together and now mode(A12:A24) works perfectly.

Also, for others with this problem, I did as instructed and removed conditional formatting (of zero to "") and just left the sumifs to output "" instead and mode ignores as you said.

Thank you both Badunit & SGIII for your time and knowledge.

Reply

Answer 4

Badunit

Level 7

30,342 points

Jun 13, 2025 7:21 AM in response to jpurk

MODE appears to return the formatted value in the cell, which might not be the same as the actual value in the cell. If 0 is the most prevalent value but the cells are formatted to not show 0, MODE will return a null string "" (the formatted value) as the mode instead of 0.

Have you tried changing your formula in the month tables so the result will be the null string "" or a dash "-" instead of 0? MODE ignores text. If the "0" results are text and not a number they will be ignored.

Reply

Answer 5

jpurk Author

Level 1

4 points

Jun 23, 2025 1:50 AM in response to SGIII

Thank you SG for taking the time to reply. I feel like you've given me enough clues to do further investigation. I say clues and not the whole answer because I need to find out more about the Filter function as my version of numbers doesn't seem to have it. Also in my version the mode function behaves differently.

I agree with you that a picture..... so I have recreated the issue in the simplest form I could as the actual SS is massively over complicated. Below hopefully you will see my data table (Table 1) and my analysis table (Table 2).

Plus the formulas for two of the calculating cells.

So the reason I thing my mode is not acting like yours is if the most common result being 'moded' is blank then it returns blank where as yours seems not to. In my simplified example below I've left the sumifs cells showing zero for you to better see the issue but in my actual SS I've formatted zeros to blanks. As you can see in Col F it shows zero, which is technically right but I'd have thought it would omit blanks.

Again, your guidance is highly valued.

James

[Edited by Moderator]

Reply

Answer 6

jpurk Author

Level 1

4 points

Jun 13, 2025 7:55 AM in response to Badunit

Hmm, that’s odd as my very first iteration output “” (blank) if the sum was zero but mode errored with something like ‘found a string when mode expects a number, duration or date’

Do you think it’s my version of numbers that’s making the functions behave differently from yours or have I missed something obvious?

Reply

Answer 7

jpurk Author

Level 1

4 points

Jun 13, 2025 12:00 PM in response to Badunit

Oh sweet. I can totally do that. Thank you both for investing your time to help a stranger.

I will make the change when I return to work on Monday and feedback the results in case anyone else has the same problem in the future.

Thanks again Badunit.

Reply