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]

What is the expected answer? Also, can you confirm that you see curly brackets around the formula?

 

[bmsmith311]

What is the expected answer? Also, can you confirm that you see curly brackets around the formula?

Yes, I do see the curly brackets.

Your formula and example actually shows the most common occur genre (on your example, you were coming up with romance) -- when I plugged your recommendations into my sheet, it either showed error or blank, depending on what we tried, but it still hasn't populated with the most frequently occurring value (on my spreadsheet with my data entered, mystery/thriller/suspense). I'm sorry it's proving to be difficult! I don't know what I'm doing wrong when you're getting good results.

 

[KRice]

Are you able to install the XL2BB add-in (see link in my signature block). This allows you to select a portion of your worksheet and create a "mini-sheet" from it that can be pasted into your post. Alternatively, you could upload the workbook elsewhere (Dropbox, Google Drive, etc.) and share the link. I'm not sure what is causing the issue without further investigation.

 

[bmsmith311]

Are you able to install the XL2BB add-in (see link in my signature block). This allows you to select a portion of your worksheet and create a "mini-sheet" from it that can be pasted into your post. Alternatively, you could upload the workbook elsewhere (Dropbox, Google Drive, etc.) and share the link. I'm not sure what is causing the issue without further investigation.

I tried to figure out how to install the XL2BB when I registered for the site but I couldn't really figure it out based on the instructions provided / my version of Excel / being a Mac user.

I'd be happy to share the sheet on a Dropbox! Reading Log - Brittany Smith 2022.xlsx

 

[KRice]

It seems that the H4 cell is corrupted somehow. Even after deleting all sheets but one in your workbook and deleting most of your table to reduce the number of potential issues, I still could not get the formula to calculate correctly. I did get a useless message upon opening the workbook for the first time---a message stating that there is a circular reference error, but the location could not be provided...no help. Anyway, I did finally get the formula working. In any cell that has a working formula, select it and copy it to the clipboard, then paste directly into H4, overwriting everything in that cell. Confirm that some type of answer is produced. Then try copying and pasting the desired formula into H4. Here's the version I used, which refers to the publication date "day" to confirm that a number is present as a condition for using only those rows.

=INDEX(I14:I26,MODE(IF(ISNUMBER(L14:L26),MATCH(I14:I26,I14:I26,0))))

You will need to adjust the ranges back to 450 rather than 26 as shown.

 

[bmsmith311]

It seems that the H4 cell is corrupted somehow. Even after deleting all sheets but one in your workbook and deleting most of your table to reduce the number of potential issues, I still could not get the formula to calculate correctly. I did get a useless message upon opening the workbook for the first time---a message stating that there is a circular reference error, but the location could not be provided...no help. Anyway, I did finally get the formula working. In any cell that has a working formula, select it and copy it to the clipboard, then paste directly into H4, overwriting everything in that cell. Confirm that some type of answer is produced. Then try copying and pasting the desired formula into H4. Here's the version I used, which refers to the publication date "day" to confirm that a number is present as a condition for using only those rows.

=INDEX(I14:I26,MODE(IF(ISNUMBER(L14:L26),MATCH(I14:I26,I14:I26,0))))

You will need to adjust the ranges back to 450 rather than 26 as shown.

Thank you! I did everything you advised (for H4 and H5 -- we were messing with H5 but I'm not sure if that was the one that was the error, but I did for both). I put your code as is back in H5 and it showed the desired result when it was only filtering I14:I26, but when I changed I26 back to I450, I'm getting 0 again.

Would it be easier to make a (forgive my lack of proper excel speak, forgot the proper term) "helping table"? I can put all 12 results into a new table and use the same formula in H5 but for the 12 results in one consecutive column? I was trying to avoid extra side info, but I can always shove it far off to the side where other users wouldn't be able see it anyway.

I appreciate all your help!

 

[bmsmith311]

Thank you! I did everything you advised (for H4 and H5 -- we were messing with H5 but I'm not sure if that was the one that was the error, but I did for both). I put your code as is back in H5 and it showed the desired result when it was only filtering I14:I26, but when I changed I26 back to I450, I'm getting 0 again.

Would it be easier to make a (forgive my lack of proper excel speak, forgot the proper term) "helping table"? I can put all 12 results into a new table and use the same formula in H5 but for the 12 results in one consecutive column? I was trying to avoid extra side info, but I can always shove it far off to the side where other users wouldn't be able see it anyway.

I appreciate all your help!

This worked! I threw the subtotals into a table and had the ones that throw errors (due to lack of information) turn up a blank cell so the final value (the year total) will display what I want it to. Then I went back to H5 and just had it equal the result of my new table. A little work around but it'll do!

 

[KRice]

I was able to get the formula working with the original layout. I believe the issue was due to the mysterious circular reference error, which I managed to track down through some trial and error. At the bottom of the September table, the summary Genre formula in I340 should reference ranges running from rows 310:339 but it was coded as 310:399, so the formula was including itself which produced the error. I've fixed that issue and made several edits to the Reading Log worksheet, as follows:

1. I was concerned that the use of merged cells might be causing a problems (I've encountered this before). Generally, I try to avoid merged cells, especially in they are included in a range referenced by a formula (your monthly table headings are). To achieve the same appearance, I unmerged the cells and then, while all relevant cells were still selected, chose format as Center Across Selection. The headings now occupy the same space/position, but each column is continuous.
2. After resolving the circular error mentioned above, I can see no reason why using this type of construction should be problematic for the local summaries below each monthly table:

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

   ...so I revised the column I and J formulas to use this construction. The same cannot be said for the overall yearly summary in H5:H7. The issue is that each monthly table is a formal Excel table (I didn't realize this from your initial post). I noticed the top header row for the table is hidden, and as I scrolled through the worksheet, I noticed in the top menu bar that the Table Design option toggled on and off as I passed from one table through blank cells into the next table. The implication of these separate tables is that each of them has column headings that are identical. For example, the "Genre" column that we refer to as column I actually has a column heading name of "Column6" in all of the tables. So any MATCH formula that looks down the entirety of column I from I14:I450 will find 11 Column6's (the 1st is excluded because it is in I13). So within each month, the above formula is fine, but for the yearly summary, another column is needed to indicate which rows contain genre entries. Initially I thought column B would be ideal, but after seeing how the column is used to establish placeholder rows for future entries, I think column M (the publication year) might be better. I'm assuming that for any entry in the table, a publication year would be known. This leads to the following type of formula for the yearly summaries in H5:H7:

   =IFERROR(INDEX(I14:I450,MODE(IF(ISNUMBER(M14:M450),MATCH(I14:I450,I14:I450,0)))),"")

   We've visited this formula before, but it seems to work fine...and I wonder if the circular error was the root cause of the problem.
3. I added a helper column AB where I entered "Monthly Summary" on the row below each month (where the summary data are located). The reason for this is that it can be used to simplify several formulas in rows 3:7. Regarding those yearly summary formulas, I changed only H5:H7, and then I've included other suggestions to the left and right of the summary block. Rather than create formulas with many cell or range references, it is often more convenient and less tedious to write a formula that applies to an easily confirmed range and then use some filtering strategies to extract only the values in that range that are desired. To that end, we can do something like this for the Total Number of Pages:

   =SUMIF($AB$14:$AB$450,"Monthly Summary",N14:N450)

   rather than this:

   =SUM(N44,N81,N118,N155,N192,N229,N266,N303,N340,N377,N414,N451)

   ...and here you'll see how the helper column AB with its "Monthly Summary" labels is put to use.
4. Similar suggestions are found near the original formulas for your consideration. This type of approach is beneficial when auditing and maintaining the formulas.

I expanded all of the tables and then hid most of the blank rows so that the worksheet collapsed down to a more manageable size. You will probably need to re-confirm the formulas by selecting the cell and reentering with Ctrl-Shift-Enter to convert the formulas to array formulas. I'm using Excel 365, which natively handles array formulas without this extra step.

The revised version is here:

Dropbox

www.dropbox.com

Post back if you encounter any issues or have any questions. Regarding your work-around...that's great!...and it actually begins to look more like the approach that I would take. I prefer to log data in one table (and in this case, a new column would contain the month associated with the entry). All entries would exist in a single flat table and there are no duplicate column headings, no below-table summary rows, no blank rows, etc. Then you could use formulas or Power Query to extract whatever information you'd like from the large flat table to create smaller, more manageable monthly or yearly summaries.

 

[bmsmith311]

thank you SO much for all your work on this! I managed to get that table to work, although when I transferred it to my "personal" version vs the version I'm sharing with customers, I had a random error.... I gave up on that haha! Maybe the circular error is affecting something there somehow too. I'll play around with your solutions as well because it's always helpful to have other ideas in my back pocket and maybe see what works better down the road if I ever need to update it. A few little things that I didn't realize could cause issues too, so I learned a lot from your help! Much appreciated!!

 

[KRice]

I'm happy to help. Post back if you have any questions about any of my suggestions. I just got another circular reference error upon opening the file...I thought that was resolved, but there must be another issue hiding somewhere.

I forgot to mention: I noticed another error on the last worksheet (Review Log) in row 20. I don't know if anything I did caused it, but check your original to see if the references in those formulas need to be revisited. If I uncover anything else, I'll let you know.