Help with COUNTIFS

Yes, as you note, the fact your dimensions are of unequal size means COUNTIFS won't work, you can revert to:

Formula:

Please Login or Register  to view this content.

however, I would strongly advise you refine your ranges from entire columns to relevant rows, either c/o a dynamic named range or, if you know your dataset won't be bigger than "x" rows where x is relatively small (i.e. few k) use x

Formula:

Please Login or Register  to view this content.

For these sorts of exercises you'd be better creating a normalised table of data so that you can more easily analyse your data.

You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it. Yours exhibits all those features.


You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

So before you get too far with this I'd create a single sheet database that contains columns for

Pupil Name
School
Preference *
Block *
Subject *


The * values could be Validation drop down cells from which you could pick values. The way I normally arrange these things is to have a single data entry row above the database in which the new values are entered, then a button which runs a macro that adds the new record to the database.

If there's a one to one mapping between Pupil and School then the School could be a VLOOKUP formula to a separate table of Pupil & School