Array formulae that allow for two conditions

[AceCo55]

I'm using Excel 2016

Worksheet "Individuals": numeracy data from students across four year levels. Each student has a status of either "QS" or "C"

I'm trying to create tables on separate worksheets for each year level and status combination
(eg Year 4 - QS, Year 4 - C, Year 5 - QS, Year 5 - C ..... etc)

I have been able to create an array formula on worksheet "Yr4-All "that pulls only the year 4 students and data from worksheet "Individuals"

BUT ....
When I can't create an array formula in worksheet "Yr4-QS" that only gets the Year "4" students with a Status of "QS"
I tried to add AND(Individuals!$C$2:$C204=4,Individuals!$D$2:$D$204="QS) to the formula but I keep getting errors


I have attached a sample file


Any help solving this would be greatly appreciated

[Root_]

Try using

(Individuals!$C$2:$C204=4)*(Individuals!$D$2:$D$204="QS")

instead of AND(...)

[AceCo55]

Thank you very much for the suggestion - Row 2 works but I get an "N/A" error when I copy the formulae down the rows?

[Root_]

That's because of a missing dollar sign:

(Individuals!$C$2:$C$204=4)*(Individuals!$D$2:$D$204="QS")

[AceCo55]

Brilliant!!!
Works just as you knew it would!

Thank you so much for your help and willingness to share your expertise.
Very much appreciated