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]

I was mistaken in my last post about the circular reference error. I had opened an earlier version of the workbook. The current version that I posted appears to be fine, with no circular reference errors. I've flip-flopped on the H5:H7 formulas: perhaps it is better not to make formulas contingent on users filling out certain publication details (such as publication year). I would think it more likely that the monthly numbering in column B will be present, and since there are no numbers elsewhere in column B, the formulas can be made to work with it. The only issue is that column B appears to be prenumbered, so numbers are present even when no book entries have been made. This means MATCH will be trying to match blanks to blanks, which will produce errors. These errors are not a problem in the formulas below each monthly table because the conditional that checks whether cells in that same column are not blank effectively ignores the errors. But for the yearly summaries in H5:H7 that look at much larger ranges across column headings (which contain info we want to ignore), these MATCH errors need to be handled differently. In this version, I've wrapped the MATCH function locally with an IFERROR function to return the word FALSE where errors occur. Then the array of matching array index numbers and FALSE entries is passed to the IF function, where any array elements corresponding to B column positions that do not contain a number will also be overwritten with values of FALSE. Finally, this array is passed to the MODE function, and since the array now contains only relevant array element positions and FALSE values (which are ignored), MODE will return the desired array index number. Here is a portion of the reading log showing these formulas. In this example, I deleted all book entries and noted that H4 reports 33% progress. The formula is counting column headings (not desired). Suggested replacement formulas are shown to the left and right of the original summary block, and one of them addresses the H4 issue.

Reading Log - Brittany Smith 2022_v6.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
3	for E cells			Reading Log Start Date:	1/1/2022		Yearly Reading Challenge Goal:	100	Number Re-Reads:	0	0	0	either of these for J3 (L3 preferred, no helper col needed)
4	0			Total Number of Books Read:	0		Reading Challenge Progress:	33%	Number Series:	0	0	for H4
5	0			Total Number of Pages:	0		Top Genre:		Number Stand-Alone:	0				0	for E5 if using helper col AB (prefer A5 formula)
6	0			Total Number of Audiobook Minutes:	0		Top Format:		Number Owned:	0	0	for I6		0	for E6 if using helper col AB (prefer A6 formula)
7				Average Rating:	#DIV/0!		Top Age Range:		Number Borrowed:	0	0	for I7		see A7 formula for avg rating (blank when sheet is empty)
READING LOG BY YEAR (2022)

Cell Formulas
Range	Formula
L3	L3	=COUNTIF(Y14:Y450,"Yes")
K3	K3	=SUMIF($AB$14:$AB$450,"Monthly Summary",Y14:Y450)
K4	K4	=A4/$H$3
J3	J3	=SUM(Y44,Y81,Y118,V155,Y192,Y229,Y266,Y303,Y340,Y377,Y414,Y451)
J4	J4	=COUNTIF(F14:F450,"YES")
J5	J5	=COUNTIF(F14:F450,"NO")
J6	J6	=COUNTIF(Z14:Z450,"Owned (backlist/older)")+COUNTIF(Z14:Z450,"Owned (new/recently acquired)")
K6	K6	=SUM(COUNTIFS(Z14:Z450,{"Owned (backlist/older)","Owned (new/recently acquired)"}))
J7	J7	=COUNTIF(Z14:Z450,"Borrowed (Library)")+COUNTIF(Z14:Z450,"Borrowed (Friend/Family)")
K7	K7	=SUM(COUNTIFS(Z14:Z450,{"Borrowed (Library)","Borrowed (Friend/Family)"}))
N5	N5	=SUMIF($AB$14:$AB$450,"Monthly Summary",N14:N450)
N6	N6	=SUMIF($AB$14:$AB$450,"Monthly Summary",Q14:Q450)
A4	A4	=SUMPRODUCT(ISNUMBER(B14:B450)*ISTEXT(D14:D450))
A5	A5	=SUM(IF(ISNUMBER(B14:B450),N14:N450))
A6	A6	=SUM(IF(ISNUMBER(B14:B450),Q14:Q450))
A7	A7	=IFERROR(SUM(IF(ISNUMBER(B14:B450),S14:S450))/A4,"")
E4	E4	=COUNTA(D14:D21,D51:D63,D88:D101,D125:D137,D162:D191,D199:D228,D236:D265,D273:D302,D310:D339,D347:D376)
E5	E5	=SUM(N44,N81,N118,N155,N192,N229,N266,N303,N340,N377,N414,N451)
E6	E6	=SUM(Q44,Q81,Q118,Q155,Q192,Q229,Q266,Q303,Q340,Q377,Q414,Q451)
E7	E7	=AVERAGE(S51:S63,S14:S21,S88:S101,S125:S154,S162:S191,S199:S228,S236:S265,S273:S302,S310:S339,S347:S376,S384:S413,S421:S450)
H4	H4	=COUNTA(D14:D450)/H3
H5	H5	=IFERROR(INDEX(I14:I450,MODE(IF(ISNUMBER(B14:B450),IFERROR(MATCH(I14:I450,I14:I450,0),FALSE)))),"")
H6	H6	=IFERROR(INDEX(W14:W450,MODE(IF(ISNUMBER(B14:B450),IFERROR(MATCH(W14:W450,W14:W450,0),FALSE)))),"")
H7	H7	=IFERROR(INDEX(J14:J450,MODE(IF(ISNUMBER(B14:B450),IFERROR(MATCH(J14:J450,J14:J450,0),FALSE)))),"")