How to sort and count?

[geeesus]

Dear all

This is my first visit to your forum and I am very impressed with the amount of knowledge being shared.

I am facing a hurdle and hope someone can help me.

I have a huge chunk of personnel data of which are broken up into many groups. Within each group there are two sub groups and all personnel are graded with Scores 5, H4, 4, L4, 3 respectively.

I need to count under each Main Group, Group A how many people have scored 5s, H4, 4, etc and likewise for Group B.

Please find attached a working spreadsheet with one Main Group and I would like to thank in advance for the help rendered.

[Paul]

Hi Geeesus, and welcome to the forum.

First, change the values in C19:G19 to simply 5, H4, 4, L4 and 3.

Then in C20, use the formula:

=SUMPRODUCT(($D$4:$D$17=$B20)*($G$4:$G$17=C$19))

Fill that to the right to G20, then fill it down to C21:G21. That should do it.

Hope that helps!

[sglife]

I think this is what you need

Please Login or Register  to view this content.

*Press Ctrl + Shift + Enter.

[geeesus]

Dear Paul

Thanks a Million! You have helped solved a real headache for me!!!!

[JBeaucaire]

You could select the cells C19, E19 & G19, then select FORMAT > Cells > Custom > and manually type in:

"Score "#

Then select D19 & F19 and format them to show:

"Score "@

Now, even though you only type 5 in cell C19, it will show Score 5 like you're used to seeing, etc...

(This is intended to be used with PJOAQUIN's answer)

[geeesus]

Thanks SG Life.... your tip has worked greatly also!