VB Macro for counting data & output result to spreadsheet

Hello Excel gurus-
thanks for reading-i hope it is clear-i did my best; please let me know if it is not.
I have a matrix with a mix of various data that I would like to count and output to a spreadsheet. it is a bit of a challenge-way beyond me.
The data consists of Shelves, Boxes and Books. The shelves hold boxes which hold books. The books and boxes need to be counted and populated to a spreadsheet.
I have an Excel file with two tabs; first tab has the quantities of BOOKS that go in each BOX, the quantity of BOXES on each SHELF, and condition of the BOOKS . Second tab has the tabulated count.
I am looking for a Microsoft VB that would give me the count in a specific format shown in tab two? I can pre-populate the headings manually; but need VB code to start at second row and on.
I know it is a ton of work and a challenge –Thanks Much in advance for volunteering your time and effort. Thanks!

please dont recommend that i do this different. i know some of you probably have better ways of displaying the resut and so forth and i know some of the data is repeated over to the result tab-that is ok . i just have to do it this way for certain reasons that i wont get into to keep this short thanks and sorry.

one note to add if it helps:
In the matrix data-you will notice that some of the books don’t have a quantity and doesnt specify which box they go into. These books do not get added to the count even though they have a BOOK STATUS i.e, Bad, Good etc. Only books having quantity are consider. sorry for this ugly and complicated mess.

hi Red fuji, I did not understand what data will be present on the result already sheet but if counts are the only missing data then it could be done this way, press Run button. I've noticed that some results of yours were wrong to my understanding. Please check attachment.

Originally Posted by watersev

please check attachment, press Run

works great ! thank you !!!

now i am trying to learn each and every line of the code in order to translate it to different work. being a VBA novice, I am having a hard time comprehending the code.
MIght be too much to ask- but do you think it can be written in a more simple way for idiot like me to grasp- it doesnt matter if it gets longer just want it to be as intuitive as possible for easy revising. dynamics such as changing data, columns and rows and additinal data and such. the core VBA will stay intact-just need to match it up to changes. thanks again.