Count by attributes

[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
D24000

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

Any suggestions??

Thanks

Clive

 

[Bob Phillips]

=SUMPRODUCT(--(A2:A4000=1),--(C2:C4000="Men"),--(D24000="Part time"))

 

[Clive_S]

Hi Bob

What or Why are there -- inside the brackets???

Many thanks

Clive

 

[Dave Peterson]

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

ps.

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

 

[Clive_S]

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

 

[Clive_S]

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

 

[Roger Govier]

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"),--(D24000="Full time"))
If you are allocation a factor of 0.5 for Part time then
=SUMPRODUCT(--(A2:A4000=1),--(C2:C4000="Men"),--(D24000="Part time")*0.7)

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

=SUMPRODUCT(--(A2:A4000=1),--(C2:C4000="Women"),--(D24000="Full time"))
If you are allocation a factor of 0.5 for Part time Women, then
=SUMPRODUCT(--(A2:A4000=1),--(C2:C4000="Women"),--(D24000="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

 

[Bob Phillips]

You can Roger

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

 

[Roger Govier]

Of course, Bob.
Stupid of me!!