Totalling entries in different columns

[scarletpinkpoppy]

Guys hope you can help, very new to Excel and failing miserably with
formulas at the moment!

I have a spreadsheet with a fair number of columns. Its split into
Job Titles, Names, Regions and training modules. I then mark x in the
box of which town will hold which training course, see below

Name Job Office Mod1 Mod2 Mod3

Jo Bloggs Engineer London x

Flo Green Sales Manchester x x

Jon Brown Engineer Birmingham x

Pat Orange Admin London x x

What I need to see is how many people are attending each office and
then split into job types. So I have say 6 people going into London of
which 2 are engineers, 6 are admin, 4 are sales etc?

I am trying to achieve how many people will be in each location, how
many modules they will be attending so I can work out how long the
training will take and how many trainers I will need? Is this possible
or am I asking the impossible

Thanks

 

[Bob Phillips]

=SUMPRODUCT((A2:A200="Joe")*(B2:B200="London")*(C2:C200="Engineer")*(D2:H200
))

etc.

Best to set the values up in cells, and refer to those with say

=SUMPRODUCT(($A$2:$A$200=M1)*($B$2:$B$200=N1)*($C$2:$C$200=O1)*($D$2:$H$200)
)


which is also easier to copy

 

[aidey]

Ahh - I have to catch a train in 1 minute, but the answer is... use a
pivot table. Try the Pivot Table wizard, put Office in the Row box,
put Job in the Column box, put Mod1, Mod2 and Mod 3 in the Data box -
and Bob's your uncle.

Aidey