Couting data

[Garry]

I have a list of data A1130. ColA=Employee; ColB=Dept;
ColC=Rank; ColD=Group
I have a grid F2:O9. F3:F9 shows all ranks. G2:O2 shows
all 6 Groups.
I want to be able to type in a group no in ColD and have
this counted in the grid against rank.
For example, there could be 4 Chief Executives in Group 1,
or 7 Directors in Group 2.
How do I return count?

Thanks
Garry

 

[Nick Hodge]

Garry

I would set up a hidden column E with the Rank and Group Concatenated.

So in E2 you would have

=C2&D2

You could then use this in your count matrix by entering in G3 (Top left
cell in matrix)

=COUNTIF($E$2:$E$130,$F3&G$2)

If you carefully not the absolute references, you should now be able to copy
this all over the matrix

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Garry]

Nick,

Excellent idea and worked perfectly.
I had tried countif but never considered the hidden column
approach. Nice one!

Thanks

Garry