Find more recurring text in same column but skip specific cells?

[bmsmith311]

Hi there! I made a spreadsheet and I'm trying to have it autocalculate data, but I'm looking to find the most often recurring text values. This spreadsheet is broken down by month and I'm trying to have one box give a "running total" for the year as the user fills in the data, but obviously we aren't going to have any data for the later months until later in the year so without placeholders, it'll throw an error code. Here is what I currently have:

=INDEX(I14:I450,MODE(IF(I14:I450<>"",MATCH(I14:I450,I14:I450,0))))

where the I column is showing a particular genre of a book that a person read. For this formula, as I understand it, I can't skip cells or select only certain ranges within the same column, so while it will ignore zero or blank, it's also including each month's totals (which will be errors until those months are filled in) and I think that's what's causing the error for my "grand total" cell.

Is there a way to skip cells that show an error? I tried a few things after a google search but none of them seemed to work.
Thank you!

 

[KRice]

The images are difficult to see, but if I'm not mistaken, column B has numbers on every row you want to consider, so you could consider using that to filter out blank rows and any summary rows that appear after each month (because those have nothing in column B). And then to suppress the error before anything is filled in, you could wrap the entire formula with an IFERROR to return a blank:

=IFERROR(INDEX(I14:I450,MODE(IF(ISNUMBER(B14:B450),MATCH(I14:I450,I14:I450,0)))),"")

 

[bmsmith311]

The images are difficult to see, but if I'm not mistaken, column B has numbers on every row you want to consider, so you could consider using that to filter out blank rows and any summary rows that appear after each month (because those have nothing in column B). And then to suppress the error before anything is filled in, you could wrap the entire formula with an IFERROR to return a blank:

=IFERROR(INDEX(I14:I450,MODE(IF(ISNUMBER(B14:B450),MATCH(I14:I450,I14:I450,0)))),"")

Thank you for your reply! I'm sorry the images are hard to see -- I ended up having to size them way down in order to be accepted ?

That seems like a brilliant work around! I tried the formula and it's not showing an error but it's just showing a blank cell. I was hoping to allow it to get the most common value entered so far so people would be able to see it as the year progresses. Open to any other suggestions! Thank you!

 

[KRice]

Could you post the formula that you are using and confirm that column B does contain numbers? The formula worked fine in a small example worksheet that I made. For example, go to an empty cell and reference a "number" in column B and enter:
=TYPE(B29)
...and confirm that it returns a 1 (which means B29 is a number).

 

[bmsmith311]

Could you post the formula that you are using and confirm that column B does contain numbers? The formula worked fine in a small example worksheet that I made. For example, go to an empty cell and reference a "number" in column B and enter:
=TYPE(B29)
...and confirm that it returns a 1 (which means B29 is a number).

Absolutely! Here is a screenshot & the code I was using //

=IFERROR(INDEX(I14:I450,MODE(IF(ISNUMBER(B14:B450),MATCH(I14:I450,I14:I450,0)))),"")

And yes, confirmed that column B is registering as a number (performed the test and it did come back as 1).
Thank you!

 

[KRice]

Does the range I14:I450 contain the Genres that you want to consider? If the range contains no data, the MODE/MATCH part of the formula would produce an error, and the IFERROR wrapping then displays a blank rather than the error message. Try removing the IFERROR wrapping...the IFERROR( and the ,"") at the beginning and end of the formula and let me know what you see. Here's a small mockup showing what mine looks like:

MrExcel_20220430.xlsx
	B					G	H	I
6							Top Genre	Romance
7
8
9
10
11
12							Title Bar
13								GENRE
14	MONTH
15	1							Mystery/Suspense
16	2							Romance
17	3							Mystery/Suspense
18								Mystery/Suspense
19								Something
20
21							Title Bar
22								GENRE
23	MONTH
24	1							Mystery/Suspense
25	2							Biography
26	3							Romance
27	4							Mystery/Suspense
28	5							Mystery/Suspense
29	6							Romance
30	7							Romance
31	8							Romance
32	9							Romance
33							Mystery/Suspense	Mystery/Suspense
34							Something	Something
Sheet5

Cell Formulas
Range	Formula
I6	I6	=IFERROR(INDEX(I14:I450,MODE(IF(ISNUMBER(B14:B450),MATCH(I14:I450,I14:I450,0)))),"")

 

[KRice]

Did the original formula you posted work okay? One other thing occurs to me...since you are using Excel 2019, was the formula entered as an array formula...with Ctrl-Shift-Enter? To try that, with the cell containing the formula selected, go the formula bar and hit Ctrl-Shift-Enter. Curly brackets should appear around the formula (they are not manually typed in).

 

[bmsmith311]

Does the range I14:I450 contain the Genres that you want to consider? If the range contains no data, the MODE/MATCH part of the formula would produce an error, and the IFERROR wrapping then displays a blank rather than the error message. Try removing the IFERROR wrapping...the IFERROR( and the ,"") at the beginning and end of the formula and let me know what you see. Here's a small mockup showing what mine looks like:

MrExcel_20220430.xlsx
	B					G	H	I
6							Top Genre	Romance
7
8
9
10
11
12							Title Bar
13								GENRE
14	MONTH
15	1							Mystery/Suspense
16	2							Romance
17	3							Mystery/Suspense
18								Mystery/Suspense
19								Something
20
21							Title Bar
22								GENRE
23	MONTH
24	1							Mystery/Suspense
25	2							Biography
26	3							Romance
27	4							Mystery/Suspense
28	5							Mystery/Suspense
29	6							Romance
30	7							Romance
31	8							Romance
32	9							Romance
33							Mystery/Suspense	Mystery/Suspense
34							Something	Something
Sheet5

Cell Formulas
Range	Formula
I6	I6	=IFERROR(INDEX(I14:I450,MODE(IF(ISNUMBER(B14:B450),MATCH(I14:I450,I14:I450,0)))),"")

You are correct! And your example is exactly what I'm trying to get it to show. I removed the IFERROR portions and went back to this:

=(INDEX(I14:I450,MODE(IF(ISNUMBER(B14:B450),MATCH(I14:I450,I14:I450,0)))))

But I'm still getting the error. And yep, I had done CTRL SHIFT ENTER each time, so that shouldn't be an issue.
Do you think my "summary" boxes would be causing an issue? I used the same formula/concept at the bottom of each sub-divided section (so, each month) to show the summary after each month was over, but that's in the same column and I don't know if that would muddy things up since there are 12 of those it would have to sort through as well.

 

[KRice]

The summary boxes (those that are shaded yellow-orange and gray in my post) should not be causing any issues because they are ignored due to the ISNUMBER filtering part of the formula. For example, in the example I posted, the entire IF function part of the formula returns the array:
{FALSE;2;3;2;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;2;12;3;2;2;3;3;3;3;FALSE;FALSE;FALSE;....followed by a lot more FALSE's out to array element 450-14+1}
This array represents the array element number where the first match is found for each item in the array. The FALSE elements represent the positions that are ignored. This array is passed to the MODE function where these array element numbers are evaluated and the most common one is returned...and this value is passed along to the INDEX function to return the value at that location in the array.

Try this version, which looks for "Yes" or "No" entries in column F. Only those rows will be considered.

=IFERROR(INDEX(I14:I450,MODE(IF(((F14:F450="Yes")+(F14:F450="No"))>0,MATCH(I14:I450,I14:I450,0)))),"")

 

[bmsmith311]

Got it! I didn't think it should make a difference but figured I'd throw it out there anyway. I tried the yes/no option you provided and it resulted in a display of "0"

The summary boxes (those that are shaded yellow-orange and gray in my post) should not be causing any issues because they are ignored due to the ISNUMBER filtering part of the formula. For example, in the example I posted, the entire IF function part of the formula returns the array:
{FALSE;2;3;2;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;2;12;3;2;2;3;3;3;3;FALSE;FALSE;FALSE;....followed by a lot more FALSE's out to array element 450-14+1}
This array represents the array element number where the first match is found for each item in the array. The FALSE elements represent the positions that are ignored. This array is passed to the MODE function where these array element numbers are evaluated and the most common one is returned...and this value is passed along to the INDEX function to return the value at that location in the array.

Try this version, which looks for "Yes" or "No" entries in column F. Only those rows will be considered.

=IFERROR(INDEX(I14:I450,MODE(IF(((F14:F450="Yes")+(F14:F450="No"))>0,MATCH(I14:I450,I14:I450,0)))),"")