Need help identifying the correct formula to use

[kyjae]

Hi,

Wondering if someone can help me to solve a particular problem. An example sheet is attached.

Here's the scenario; Several people submit pictures into an Art Competition and are awarded a Grade, A to F. Grade A earns 6 points, B earns 5 points, etc down to F earning 1 point.

I know how to use the COUNTIF and SUMIF formulas to count the number of entries by each person, and each person's total score respectivley. The problem I'm having is identifying the correct formula to use to count the number of different awards, A to F, given each entrant (hence the blank cells in range E20 to J24).

Can anyone suggest an Excel built-in Formula that will achieve this and the correct way to use that formula?

Many thanks in advance.

[Pepe Le Mokko]

Enter

Please Login or Register  to view this content.

in E20 and drag down and across

[Tony Valko]

Instead of using column headers like:

No. A Awards

Use either:

A Awards

Or simply:

A

If you use "A Awards", try this formula entered in E20:

=COUNTIFS($B$2:$B$15,$B20,$D$2:$D$15,LEFT(E$19))

If you use "A", try this formula:

=COUNTIFS($B$2:$B$15,$B20,$D$2:$D$15,E$19)

Copy across to J20 then down as needed.

[kyjae]

Thank you, Pepe - Genius!!

I've spent a day or so playing around with SUMIFS and getting nowhere. Never come across the SUMPRODUCT formula before, so very grateful for your help.