Counting Cell Colours and Content

First time posting on this forum because it’s not too often I delve this deeply into Excel formulas, but I’m currently looking to adapt a spreadsheet that is currently widely used across my workplace.
I will try my hardest to give much information as possible regarding my query, so here is it:
I currently have a spreadsheet for a school that is essentially a DayPlan (attendance sheet) for what classes students will be in every day. Students change subject lines and classes so often, that these DayPlans are generated on the daily. This is what an empty DayPlan sheet looks like, as you can imagine, each day there are *unique* student names added to the sheet.
Sheet.PNG
This spreadsheet (which belongs to workbook) is then heavily locked down, simply allowing teachers to come in and use the key on the right to colourcode (format painter) the kids in their class as to whether they were present/absent, etc (with a few other options you can see).
Given our student subject lines change all the time, along with a number of other factors, we never found a suitable solution for recording attendance until this workbook. The Principal loves it, but would like to be able to generate weekly sheets from said data.
What we’re trying to do is to create a sheet in the workbook that would calculate a student’s attendance per week (ie how many lessons were they present and home many were they away).
To do this though I need a way to count colours and cell content. I have both of these separately but not together.
This tool allowed me to count colours of cells and works perfectly, however I need to sweep the whole sheet for colours *and* names. https://www.extendoffice.com/documen...-by-color.html

To put in in basic code I need something that will go:
COUNT number of cells IF cell is green AND cell contains the name ‘Bob’
If I could figure out the way to do this for present students I’m sure I could modify the function for away students and cross sheet calculations.

Hi Marvin and thanks for your response.
Unfortunately nothing in that link is quite what I need.
I know how to count and sum based on colour, but I can't seem to filer those counts based on the text *in* the cell.
You are right though in saying names would populate each lesson on that sheet.
I then want to be able to search all cells for a name and colour combination and count how many there are.

What you've said there:
---
You have John Smith in about 5 cells and you want to show him having 2 Red and 2 green and 1 Away (for example)?
Do you want to show each student (alphabetically) and then show how many of each color they have?
---
Is exactly what I want.
I could potentially put a number at the end of each name but I'm not sure how this would help calculate? I'm happy to hear your reasoning though!
I'll also provide the example below (normally there would be more names but you'll get the picture):
Pic_1.PNG
So that would be one subject line for instance and then I would want to take that info and transfer it to another sheet via a count function of some sort to show something like this:
Pic_2.PNG

Try pressing F9.

Originally Posted by JohnTopley

Try pressing F9.

Like magic!
All done here lads.
Principal is very happy.
Appreciate the help! Will mark the thread as solved.