Countif expanded

[brownti via OfficeKB.com]

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

 

[T. Valko]

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)

 

[brownti via OfficeKB.com]

PERFECT, THANK YOU!

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)

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.

[quoted text clipped - 27 lines]

row
after that. Hopefully someone understands. Thanks

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

PERFECT, THANK YOU!

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)

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.

[quoted text clipped - 27 lines]

row
after that. Hopefully someone understands. Thanks