Matrix Lookup Count

[MarkMcG]

I have the following table:

People Jack Jill Fred
Role
Service P B
Support P B
Service C
Operations B P
Operations B C P

I need a function that can sum the number of each (P, B or C) for Role and
People given P = 3, B = 2, C = 1.

Role Sum by People

Role Service Support Operations
People
Jack
Jill 2 3 3
Fred

(Note, I only show Jill's Sum of Roles)

Any help would be appreciated. I am open to replacing P, B and C with 3, 2
and 1. Just need the formula to do the trick.

 

[Sandy Mann]

With the data supplied in A17 aso that Jill's data is in C3:C7 and the
table you want in F1:I4 so that the "Service", "Support" and "Operations" is
in G1:I1 then:

=SUMPRODUCT(($A$3:$A$7=G1)*($C$3:$C$7={"P","B","C"})*{3,2,1})

Entered in G3 and dragged across to I3

However, it would be better not to transpose the tables and have - say - the
names in L1:N1 and the "Service", "Support" & "Operations" in K2:K4. With
that arrangement,

=SUMPRODUCT(($A$3:$A$7=$K2)*(B$3:B$7={"P","B","C"})*{3,2,1})

entered in L2 and dragged across and down will fill in every number without
having to adjust the formula.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[MarkMcG]

Sweet Sandy, many thanks.

 

[Sandy Mann]

You are very welcome. Thanks for posting back

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk