This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I have a 7 sheet excel national d/base with the 4th sheet working out all the stats and therein lies the problem. Lots of links have been broken. Is there a person I can send it to?
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Hi Jeovany
That is an excellent idea and I will be available for the meeting at 19:00 London time Thursday 20 March 2025
Many thanks for taking the time.
Regards
Keith
Hi Keith
The file in the link has the formula corrections based on your new logic requirements.
Archived Database-BBRC (Jeovany Fix 16-03-2025).xlsx
Due to the scale of the project and your goals, (to save some time in Q&As), please, consider having a video chat meeting, let's say on Thursday 20th March, at 7:00 pm (19:00) London local time.
If you agree, I'll send you the link to the meeting close to the scheduled time.
Regards
Jeovany
Hi Jeovany
I think OK same is not the problem! I have typed in AI in cell E2 on the stats sheet and it returns a total of 64. This is wrong it should be 80.
In the pre 1950 sheet if you select Alpine Swift (col Q) and select OK (col C) you will see that the following Rows 297, 305. 317, 320, 330, 341, 346 & 347 all have totals with more than one in and if you take one from each of these rows you will have 16, which is the difference, so it is still not counting records with more than one in the Count column.
Its the same in The Records sheet when you select Alpine Swift and OK and auto-sum it gives a total of 486 which is not what the stats sheet displays.
I am working to trim down the progress column along with many things but believe that the stats are really important to have correct.
So, I would be very grateful if you could resolve this, please.
Regards
Keith
Would you please supply me with the extra bit of code to add into the formula.
Regards
Keith "> Hi Jeovany
The database is not adding the records up correctly. I believe the problem lies in the fact that the code is not using the column Count which can have numbers more than one in there. For instance when you type in AW in cell E2 the result is 2 short in cell D91 because two records have a two in the cell in sheet pre1950. The same applies to the Records sheet.
=COUNTIFS(pre1950tb[BTO],Specific_Species,pre1950tb[Progress],"OK",pre1950tb[CountyCode],$A59,pre1950tb[Year],"<1950")
Would you please supply me with the extra bit of code to add into the formula.
Regards
Keith
Hi Keith
The formulas are correct, and here is the reason:
On the pre-1950 sheet, in rows 93 and 94, in the Progress column, was entered "OK same" shown in the highlighted yellow cells. The formula only counts the "OK" values, these 2 lines of data are missing in the count.
The "To be Revised " sheet has nothing to do with the formulas, and is not used in any calculations.
The sheet was added simply, to alert you about the many mistakes/typos/alterations entered in the Progress column that could affect the accuracy of the calculations, exactly as pointed in the above comments and pictures.
I explained that in a previous post.
Here is the list of the many OK types entered in the Progress column, repeated multiple times, and surely not counted in the Stats sheet (shown in the "To be Revised " sheet)
| OK (maybe) |
|---|
| OK (nothing to change) |
| OK ?SAME |
| OK Agg |
| OK as alb sp |
| OK AS GP |
| OK as group |
| OK as intergrade |
| OK as pair |
| OK as pr |
| OK as sp |
| OK CAT E |
| OK group |
| OK returing bird |
| OK Return |
| OK return/same |
| OK returnee |
| OK same |
| OK same |
| OK Same as 2021 |
| OK same as highland |
| ok same as J Mccullum birds |
| OK same as nmb |
| OK Same as scilly |
| OK same prev published |
| OK same-at BOURC |
| OK, same as 10701 |
| OK-at BOURC |
| OK-Cat D |
| OK-Cat D same |
| OK-Cat E |
| OK-Cat E same |
| OK-Channel Isles |
| OK-Channel Isles same |
| OK-ex BBRC |
| OK-Irish |
| OK-not British Waters |
The formula I previously used follows the basis of your original file.
If you want to include/count all the ones that contain the word OK in the Progress column, then
We need to replace in all the formulas the "OK" with "*OK*"
You may download the file with the correction here Archived Database-BBRC (Jeovany Fix 16-03-2025).xlsx
Regards
Jeovany
Hi Jeovany
The database is not adding the records up correctly. I believe the problem lies in the fact that the code is not using the column Count which can have numbers more than one in there. For instance when you type in AW in cell E2 the result is 2 short in cell D91 because two records have a two in the cell in sheet pre1950. The same applies to the Records sheet.
=COUNTIFS(pre1950tb[BTO],Specific_Species,pre1950tb[Progress],"OK",pre1950tb[CountyCode],$A59,pre1950tb[Year],"<1950")
Would you please supply me with the extra bit of code to add into the formula.
Regards
Keith