Simple problem for you all....killing me though

  • Thread starter Thread starter sfphen
  • Start date Start date
S

sfphen

Must be a COUNTIF or similar function
DATA is LOCATION (A, B, C) and JOB TITLE (X, Y, Z)
I figured how to get the # of people at each SITE (using COUNTIF)
Need to figure out how to have subsets of this...Operators, Foremen,
Supervisors at Site A, and the same for SIte B, etc...
It can't be that hard.
Thanks
 
Assuming that your data is in 2 columns, E and F (to separate them from your
ABC and XYZ locations/titles in the formulas) and goes from row 2 to row 100:
=SUMPRODUCT(--(E2:E100="A"),--(F2:F100="X"))
Will give you number of job title X's at Location A. Just change the "A"
and "X" for each pairing.
 
Location by jobs:

=SUMPRODUCT(--(A2:A100=Location),--(B2:B100=JobTitle))

Put Location and Job Title in cells

=SUMPRODUCT(--(A2:A100=C2),--(B2:B100=D2))

Location total:

=SUMPRODUCT(--(A2:A100=Location))
 
Hi

Put a list of your job titles in cells D2:Dnn
Put a list of your Sites in E1:??1

In E2
=SUMPRODUCT(($A$2:$A$1000=E$1)*($B$2:$B$1000=$D2))
copy across and down as required.

Alternatively, use a Pivot Table.
Mark the range of data.
Data>Pivot Table>Finish
On the new tab created, drag Site from the field list to the Column area
Drag Job Title from the field list to the Row Area
Drag Job title again from the field list to the Data area
 
Location by jobs:

=SUMPRODUCT(--(A2:A100=Location),--(B2:B100=JobTitle))

Put Location and Job Title in cells

=SUMPRODUCT(--(A2:A100=C2),--(B2:B100=D2))

Location total:

=SUMPRODUCT(--(A2:A100=Location))





- Show quoted text -
Click to expand...

=SUMPRODUCT(--($G$5:$G$340=$R5),--($H$5:$H$340=S$4))
I put this in the first cell and it works!!!!
I now have a 57R x 4C array that is perfect for all 336 entries.
Thanks so much for the selfless giving of yoru time to make my day.
 
Thanks for the feedback.

[email protected] said:
=SUMPRODUCT(--($G$5:$G$340=$R5),--($H$5:$H$340=S$4))
I put this in the first cell and it works!!!!
I now have a 57R x 4C array that is perfect for all 336 entries.
Thanks so much for the selfless giving of yoru time to make my day.
Click to expand...
 
Back
Top