Countifs across tabs and every other column

Hello everyone,

I'm new to this forum but hope someone can help. (Spreadsheet Attached)

I work in a school where we have students studying OCR Courses ranging from Numeracy to ICT and everything else and there is a tab/sheet for each subject. I have built a tracker to enable us to monitor progress, each unit number has two columns a mid year which shows traffic lights (3 = green, 2 = amber, 1 = red, 0 = off/black) then next to that is a unit completed which shows a tick when the number of credits that unit is worth is entered into the cell otherwise it shows a cross.

I want to be able to count how many green traffic lights so (3's) there are overall across all my tabs in the mid year columns but have been struggling as some of the the cells under "unit completed" also contain 1, 2 and 3's.

So I essentially need it to do a countifs formula from columns D7, F7, H7 etc so every other column across every tab/sheet and show it on a summary page at the end so I can see how many green lights are in the Mid years overall. It's probably worth mentioning that every subject has a varying amount of units.

Ideally I then want to count this by year group if possible.

Sorry if this is wordy this is my first major spreadsheet I feel I have done well so far.

5d5cbbc7-6f00-488f-b69b-46d0534a8fa9.png

Use MOD(COLUMN(D7:Y7),2)=0 to identify even numbered columns starting at column D (4th column)

You're better off posting a spreadsheet than an image, as for testing any solution we ALL have to reenter the data

Here's what I would do.
On each tab place the totals column in BU so each tab has the same format (If you don't need all the columns up to BU (e.g. Arts and Crafts - hide the columns from V to BU, adjust as necessary on other tabs)

In each tab in BV7
=SUMPRODUCT(--(MOD(COLUMN(D7:BU7),2)=0)*(D7:BU7=3))
copy down the column as far as BV1000 (allowing for 1000 pupils, choose less if you want)

Sum the BV column in BV1001
Now just sum each BV1001 cell on each tab using 3D Sum / 3D Reference

Google Excel 3D SUM or 3D REFERENCE to see how to total the same cell over multiple sheets
This should give you the figure you need.

Create a total for year 10 and one for year 11

Try (untested)

in BV7
=SUMPRODUCT(--(MOD(COLUMN(D7:BU7),2)=0)*(D7:BU7=3))*(C7=10)

in BW7
=SUMPRODUCT(--(MOD(COLUMN(D7:BU7),2)=0)*(D7:BU7=3))*(C7=11)

Copy down as before
Sum the columns

Do a 3D SUM for both totals.