PDA

View Full Version : Solved: Find value and count occurances of text



alu
08-19-2009, 02:15 AM
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
08-19-2009, 03:12 AM
Try this

mdmackillop
08-19-2009, 03:13 AM
I think SumProduct is the way to go. Have a look here (http://vbaexpress.com/forum/forumdisplay.php?f=98)

Bob Phillips
08-19-2009, 03:13 AM
With a pivot

alu
08-19-2009, 05:14 AM
Solved with the Pivot Table. Thanks El Xid.

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

Thanks again all.