I am trying to count meals in a column that has 5 possible values. I am totaling how many meals I should have and then trying to total the matches found. I am one match short in two columns I have checked the spelling of the items and don’t see anything so I suspect one of the items has something hidden in the column. How can I find the unmatched item. I am attaching the spreadsheet and the columns I am having trouble with are I (should have 72 total) and column L should have 8 meal, not 9. I appreciate any help you can give me.
2025 LINDA MEMBER GUEST(V.1).ods (29.7 KB)
There is not a word misspelled.
You must check the formulas.
- The cell
I79is=COUNTIF(I3:I76;"=Chicken")
Must be
=COUNTIF(I2:I76;"=Chicken") - The cell
L79has the same problem
There is a whitespace in L68.
Your count depends on things being spelled correctly. You already have the list of meals in G78:G82, you should use these for data validity and as references in the formulas.
Data Validity
- Select cells H2:I76 and click Data > Validity
- In the dialogue, in Allow select Cell range, in source, enter the range containing the meal names,
$Sheet1.$G$78:$G$82, make sure *Allow empty cells is ticked. OK - Repeat for other meal columns
Now you cannot accidentally misspell or add a space
Formula references
In cell H78 enter =COUNTIF(H$2:H$75;$G78), press Enter. Copy that and paste it in the range H78:I82, L78:L82 and O78:O82
These formulas will always match what is in your list of meals, even if you misspell.
1 Like
Thanks for the whitespace tip. I don’t know how to find that, but deleted contents of that cell and that corrected the total. Also thanks for the tip about relative addressing. I am not new, but not a frequent user and am not familiar with all the nuances that are available.
I am going to try this. I will use this next time I have this situation. Thanks for the information.
If you do not give the rows an absolute reference ($), you will get incorrect cell references when copying down.
Relative Addressing — Absolute Addressing
There are also incorrect search criteria.
