Counting occurrences of <x> in column <a> where condition <y> occurs in column &lt

  • Thread starter Thread starter phyrephox
  • Start date Start date
P

phyrephox

I have a worksheet containing data on my performance in a racing game
Each row of data includes Race Type, City, Track Name, Medal, ... an
so on.

I would like to create a small matrix that displays the number and typ
of medals I've won for each Race Type. For example:

Race Type | Medals
|
| Steel | Bronze | Silver | Gold | Platinu
|
One on One | 0 | 2 | 51 | 12 | 1
|
Cone Challenge | 0 | 9 | 134 | 7 | 0
|

I've been trying to figure out some combination of COUNTIF with eithe
HLOOKUP or INDEX but have not been able to see my way through this.

There are 10 different Race Types (two are included in the exampl
above) and 5 different medals. The row data is all text.

What would be an efficient way to do this?

Any help and comments would be greatly appreciated.

Thank you :confused:

Note: Sorry, I couldn't figure out how to get the vertical bars to lin
up in my grid show above
 
Hi
1. one way: use pivot tables (see
http://peltiertech.com/Excel/Pivots/pivotstart.htm for some information
about this)
2. Your worksheet with the data (lets say its names 'data') starts in
A1)
3. Assuming your target sheet has in row one the medal types (starting
in B1) and in column A the race types enter the following in B2 (would
calculate steel medals for 'one to one' races):
=SUMPRODUCT(('data'!$A$1$A$1000=$A2)*('data'!$D$1$D$1000=B$1))
copy this for your target sheet

HTH
Frank
 
Back
Top