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.

" wrote:

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

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))


" wrote:

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

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.
DataPivot TableFinish
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

--
Regards

Roger Govier


wrote in message
oups.com...
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

On Jul 16, 3:58 pm, Toppers wrote:
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))



" wrote:
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- Hide quoted text -

- Show quoted text -

=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.

" wrote:

On Jul 16, 3:58 pm, Toppers wrote:
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))



" wrote:
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- Hide quoted text -

- Show quoted text -

=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.