Count by attributes

  • Thread starter Thread starter Clive_S
  • Start date Start date
C

Clive_S

Hi

I have a table with a number of values.
Each person is assigned a unique attribute (identifier), zone (1 -
43), gender and employment (full or part time)

Pivot only gives 21 or 22
I want to count the following.
All full time men in Zone 1 & get a double (21.5)
All part time men in Zone 1 & get a double (3.7)

With SumIf you can count all men part time or full time within rows
D2:D4000

I want to count all part time men within rows D2:D4000 WHERE A2:A4000
= 1

Any suggestions??

Thanks

Clive
 
Hi

The formula worked, but it counted the number. It did not add them up.
For example in Zone 1 there where 40 men full time (1) and 30 women
part time (0.5 - 0.7)

The total was 40 and 30. It should have been 40 and 15.

Thanks

Clive
 
Hi




The formula worked, but it counted the number. It did not add them
up.
For example in Zone 1 there where 40 men full time (1) and 30 women
part time (0.5 - 0.7)

The total was 40 and 30. It should have been 40 and 15.

VLOOKUP added up correctly, only problem it only did whole number 40
and 30.
It does not add doubles eg 29.5 or 30.5

Thanks


Clive
 
Hi Clive

you cannot get all results in one Sumproduct.
Leaving aside the Zone, you either have
Men Full time
Men Part Time
Women Full Time
Women Part time

Bob's formula will correctly calculate the number of Full time Men
=SUMPRODUCT(--(A2:A4000=1),--(C2:C4000="Men"),--(D2:D4000="Full time"))
If you are allocation a factor of 0.5 for Part time then
=SUMPRODUCT(--(A2:A4000=1),--(C2:C4000="Men"),--(D2:D4000="Part time")*0.7)

The sum of both above formulae will give the correct value for Men

=SUMPRODUCT(--(A2:A4000=1),--(C2:C4000="Women"),--(D2:D4000="Full time"))
If you are allocation a factor of 0.5 for Part time Women, then
=SUMPRODUCT(--(A2:A4000=1),--(C2:C4000="Women"),--(D2:D4000="Part
time")*0.5)

The results could be an integer value if then are multiples of 10 Part time
men, or multiples of 2 part time women
 
You can Roger

=SUMPRODUCT((A2:A4000=1)*(C2:C4000="Men")*(D2:D4000={"Full time","Part
time"})*({1,0.7}))
 
Back
Top