dropdown list to match a criteria

Hi everyone,

thank you for taking the time to read my post. I have a problem and i am sure a solution can be easily found, just can't get my head round it yet.
I have a grid that counts 2 grades which match a criteria, using the countifs formula.

I would like to match these grades to a class. I tried using data validation of a group to match these results but haven't got anywhere really. I just need the grid to change when a certain class is chosen. I am attaching an example of the file and would be grateful for any help received. An explanation would also be a bonus but i appreciate any time or effort anyone takes.

God bless.

Thank you

Got the solution...

put it in b3 and drag and drop..

Formula:

Please Login or Register  to view this content.

Check the attachment...


Don't forget to click *

Thank you to Vikas and newdoverman, your help has been much appreciated.

Hi back again...

the formulas worked great and i thought I should look at some youtube clips on extending this further with another dropdown list. I was wondering could it be possible to modify the formula such that whatever new heading i take into account in cell s2 and s3 is also counted in the criteria. I have created some new columns with some more data each with a separate heading, but down know how excel can choose which column data i need.

I hope there is a solution to this as i have learnt a tremendous amount by playing round and experimenting with formulas, but can't seem to finalise this last situation I have attached another sheet with the new column data and an example of what i would like to achieve.

Thank you for taking the time to read this post, best wishes.

I think i have realised that data!$D$3:$D$10 is taking the UML column into consideration, but what happens if i wanted in cell s2 , using data validation SEN, i need excel to choose column F? is there a way for this to automatically convert to

=COUNTIFS(data!$A$3:$A$10,$A15,data!$B$3:$B$10,B$14,data!$C$3:$C$10,Sheet1!$K$3,data!$F$3:$F$10,$S$3), if i have choose SEN on the drop down list IN S2 with say SA in cell S3??

I know we are near to completing this..i have been looking at named ranges on youtube, but can't link the formulas together..

Much appreciated for the time you have spent on this Mr Newdoverman.

Thank you sir again for your time and effort. It has been much appreciated I am going to work on this and get back to you as soon as i I reached my target. God bless you.