QUery related Excel formula

Excel 

I learnt more details of COUNTIFS, INDEX with MATCH function, then i got the result.

And wasn't that--getting the result yourself--far more satisfying than if HansVogelaar had written out the formula for you?

Well done! Congratulations. Learning by doing.

I learnt more details of COUNTIFS, INDEX with MATCH function, then i got the result.
I am very sorry sir

Excel 

I am learning Microsoft Excel and VBA.
It is not a homework question.

What is it, if not a homework question? I.e., a question you're expected to work to answer as part of that "learning Excel and VBA" that you are doing?

What have you tried so far on your own? HansVogelaar  gave some initial guidance on formulas/functions you could employ. We might be inclined to offer even further help, but you need to show SOME effort here beyond repeated pleas for help.

Please give me solution with attached file, so i will learn new things.

For what it's worth, I'm pretty good at Excel, but I can't just look at your spreadsheet and come up with a formula. I'd have to play around a bit, try this or that. That happens to be the way I learn how. It could  (and should) be the way you learn as well. You don't really learn--not REALLY--by having somebody else write the solution for you; you LEARN by working through trial and error. You're not going to break your computer or the Excel program by approaching this through trial and error, and you'll probably find that you learn even through things that don't work.

So have fun. Play with the functions HansVogelaar has already given you.