Using Excel 365

I hope that someone can help me with the below. Please see attached.

When the user adds a date in column L (2024 Inspection), I want to calculate the percentage inspected for each officer (in this case, it would be 50%).
Three sites were inspected out of six (cell C8).

Then I need the overall percentage (in this case, it would be 12%). Three sites were inspected out of 25 (cell C31).

Also, based on the same date entry in column L, I need to sum the total Principle Balance for each officer. In this case, it would be $3,908,828.93. (Cell range E2:E4). Then the overall total among all the officers. Since I just have the three entries, it would still be, $3,908,828.93.

Do you have a recommendation where to put all these results?



I would GREATLY appreciate any help someone could offer.

Thank you in advance,
Karen

Attached Files

Plan.xlsx (21.9 KB, 5 views)

In your table, you have inspections for 2 different years. Are [Risk Grades], Principle Balances, Available Credits, Interest Rates, etc. all same for every year? Or did you overwrite them for rows where inspection dates for year 2024 are present?

Generally, I'd have a sheet with table for data entry, where all inspection info for every site at every year are in separate rows - without any subtotal rows between data. And have report sheet(s), where you can select e.g. year, or officer, or whatever, and you get the info matching with report conditions displayed in any design you wanted.

In case you want also some subtotals to by displayed on data entry sheet, place them at top of page - above the data entry table, with Freeze Panes setting keeping all subtotals, and header of table, always visible. Set the autofilter on for the data entry table, and Subtotal formula displays the summary for filtered records. E.g. when you have a subtotal to calculate the sum of Principle Balance column, and you set the filter for table to all inspection dates of year 2024, you get the total of all entries for this year. Add another filter condition like Officer is 'MZ', and you get the total for this officer in year 2024. Etc.

Also, consider using instead or regular table the Defined Table for data entry. In Defined Tables, when a new datarow is added, all formats, formulas, etc. are automatically expanded there, so long as they are the same for the whole column.

Thank you so much for all of this helpful information. I’m working on this for someone else and cannot change the structure.

Do you know a way to accomplish what I’m looking to calculate based on the current structure?

Your help would be GREATLY appreciated.

Thank you again,
Karen

Would someone be so kind to help me?

Your help is greatly appreciated.

Thank you,
Karen

Hi Karen,

The attached should do it for you. As dates are added in the 2024 column, sums will automatically appear for each officer and also for the grand total at the bottom. I've put the percentages just to the side for you and they work. If you wanted it to look more eloquent, it would be better to have the % calculations under the sum value per officer e.g. in a new row under L8. This would then be able to be completed for previous years too, however if the principle balance changes each year then this is not the best way to track all of this (as mentioned by Arvil in a previous post).

Hope that helps and if you are struggling with anything else related to this please ask away

Best wishes,
B

Attached Files

Plan.xlsx (21.4 KB, 6 views)

Office_Sci,

Thank you soooooooo much!

I appreciate your knowledge & time.

Have a great weekend,
Karen