Countif expanded
 

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

Countif expanded
 

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

Countif expanded
 

PERFECT, THANK YOU!

T. Valko wrote:
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

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

Countif expanded
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"brownti via OfficeKB.com" <u31540@uwe wrote in message
news:8e749c2ccac1a@uwe...
PERFECT, THANK YOU!

T. Valko wrote:
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

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