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.