COUNTA formula with criteria

I have a large workbook containing many sheets looking at student progress in a school.
5 sheets are pasted into the workbook, each containing information for a year group - this is the raw data (Yr 7 Aut 1, Yr 8 Aut 1 etc.).
The information for our Senior Leaders is contained in sheets Year Groups and Subjects - every other sheets is used to work towards providing that information.

In an effort to make the workbook more easily used, I've created a DataCells sheet which "declares" which rows and columns data is held - subjects change year on year and I don't want to have to change the formulas at the start of each academic year. I have tried to create lots of validating cross checks to make sure that the data that comes in is all counted in the appropriate places SO......

On the DataCells sheet I have several COUNTA formulas in row 6 columns E,I,M,Q & U which count all data in the ranges that I want to work on. This is totalled in W6 for a total data count and cross checked on the Counts sheet AT68 which in turn is then checked against the Year Groups Sheet C3 which is finally checked against the Subjects sheet A264!!! Yes - the data needs to be correct to be useful. There are other cross checks built in but they are unimportant to my current problem.

The Counts sheet works perfectly - it is a simple COUNTIF for each year group.

The SEN COUNTS, PP COUNTS AND EAL COUNTS sheets are all variations of the original Counts sheet but they are only looking at what we call "vulnerable" students - these are identified on the Raw Data sheets (Yr7 Aut 1 etc.) in:
Column D for SEN students who have the value of K or E
Column B or F for PP Students who have a Y in either column and
Column E for EAL Students who have value of Y
I have to use COUNTIFS (for the EAL & SEN COUNTS) and SUMPRODUCT (for PP COUNTS). I still need to build the Subjects sheets using only these students but that is a little way off yet.

Now for the problem.......

Whilst these additional count sheets all look fine - I want to build the same checking mechanism into the sheets to ensure that I have captured all of the data. So I want to COUNTA the complete range of 'Yr 9 Aut 1'!J2:AT147 for example but only if the EAL column has a Y. And then of course the same for the PP Counts and SEN Counts. I just can't get my head around it - I think an INDEX MATCH with a counta may be the answer but I just can't see how.

Any help gratefully recieved

Many thanks

Sue

Thank you Alvin - that worked perfectly.

Now I just need the other two counts - SEN and PP which have move criteria. do you have any idea for those please? I've tried :

=SUMPRODUCT(--('Yr 8 Aut 1'!J2:X142<>""),--(('Yr 8 Aut 1'!F2:F142="Y")+('Yr 8 Aut 1'!B2:B142="Y")>0)) for the PP Count but I get a #VALUE error which appears to be because of the different array dimension.

Thank you

I've fixed the PP Count using this formula =SUMPRODUCT(--(INDIRECT(E5)<>"")*((INDIRECT(E12)="Y")+(INDIRECT(E8)="Y")>0))

and the SEN count with this formula =SUMPRODUCT(--(INDIRECT(E5)<>"")*((INDIRECT(E10)="E")+(INDIRECT(E10)="K")>0))

I've edited the EAL counts to =SUMPRODUCT(((INDIRECT(E11))="Y")*((INDIRECT(E5))<>"")) as well to make use of the INDIRECT stuff - no idea if this is good practice or not but it will hopefully allow me to leave to someone else to keep the data going in every 6 week.

Thank you so much - I may even understand SUMPRODUCT one day rather than just google it and play until it works!!

Thank you

Sue

Never heard of Evaluate Formula - wow, how helpful will that be when working on a complex formula!

My life at work revolves around providing data that focuses on comparing vulnerable student progress with the rest of the cohort. My guess is maybe you too if you are used to the terminology? 😁

Thank you