Solved: Find value and count occurances of text

[alu]

Hi All,

I have a list of School results to process. And need to find the number of Bioligy students who have a Pass, Merit and then Physics studetns who have Pass, Merit.

Biology - Pass
Biology - Pass
Biology - Merit
Physics - Pass
Physics - Merit


To solve this I am using an elaborate macro that filters the data and then counts it, but this involves me selecting the new data soure (subject) each time. I won't post the code for this as it was done in recorder mode and is vast.

Could anyone advise me of a better way or formula to do this. I'm thinking of some sort of =vlookup maybe?

Thanks in advance all

edit: Possibly a Pivot table thinking about it?

[Bob Phillips]

Try this

[mdmackillop]

I think SumProduct is the way to go. Have a look here

[Bob Phillips]

With a pivot

[alu]

Solved with the Pivot Table. Thanks El Xid.

Also never come across SumProduct before, looks like its worth investigation.

Thanks again all.