One way:

Entered in B2 then copied across to D2 then down to row 4:

=COUNTIF(INDEX($A$7:$C$9,,MATCH($A2,$A$6:$C$6,0)), B$1)

--
Biff
Microsoft Excel MVP


"brownti via OfficeKB.com" <u31540@uwe wrote in message
news:8e6c051a5b63d@uwe...
I cant seem to write an equation to solve the problem below. I can do it
manually by changing a countif formula, but would like it more automatic.
I
have a blank table like such:

A B C D
1 1 2 3
2 A
3 B
4 C
5
6 A B C
7 1 2 2
8 1 3 1
9 3 2 1

I would like to put an equation in cell B2 that looks at A6:C9, finds the
header in row 6 that matches A2 and then counts the occurrences of B1.
The
final table with all equations entered would look like this:

1 2 3
A 2 0 1
B 0 2 1
C 2 1 0

I dont know if this makes sense...

The current formula i use is =Countif(A6:A9,B1). then i move it for each
row
after that. Hopefully someone understands. Thanks

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200812/1