Woman age 40

[Tony_student]

Let's say one colomn is a list of different gender types (man, woman, man,
woman and so on) and another colomn a a list og ages (25, 35, 40, 35 and so
on). What formula should I use to define how many woman of age 40? Thanks

 

[Stefi]

=SUMPRODUCT(--(A2:A13="woman"),--(B2:B13=40))

Regards,
Stefi

„Tony_student†ezt írta:

 

[Tony_student]

Something is wrong with it.
Can I sand you the sheet by email?
Thanks

 

[Stefi]

For the moment I think it isn't necessary. Post an exact example of your data
and layout!

Stefi

„Tony_student†ezt írta:

 

[JLatham]

Maybe she's just not interested? Just kidding.

Stefi's formula should work, assuming that the gender entries are in column
A and the age entries are in column B.

With that type of SUMPRODUCT() formula, the row references in all parts of
it must contain the same number of rows (although not necessarily the same
row numbers).

As Stefi requested, post some data and more information. "Something is
wrong with it" doesn't tell us much. WHAT is wrong with it would be a big
help also.

Also, in testing against text (woman, man) - leading and trailing spaces are
important and if your test doesn't have them and your test data does, then
the result of the test will be failure.

 

[Tony_student]

Everything is fine now! I changed "," sign to ";" and everything works.

Here is another problem:
Define the most popular hair colorof these woman of age 40, provide the
exact number of woman with this (most popular color)
Thanks!

 

[Tony_student]

Everything is fine now! I changed "," sign to ";" and everything works.

Here is another problem:
Define the most popular hair colorof these woman of age 40, provide the
exact number of woman with this (most popular color)
Thanks!

 

[Tony_student]

Everything is fine now! I changed "," sign to ";" and everything works.

Here is another problem:
Define the most popular hair colorof these woman of age 40, provide the
exact number of woman with this (most popular color)
Thanks!

 

[Stefi]

Let column A: sex
column B: age
column C: hair color

Enter hair colors in D1:G1, say, we have 4 colors:

D E F G
black brown red blonde

In D2:
=SUMPRODUCT(--($A$2:$A$20="woman"),--($B$2:$B$20=40),--($C$2:$C$20=D$1))

Copy to the right to G2,

In H2:
=INDEX(D1:G1,1,MATCH(MAX(D2:G2),D2:G2,0))

Regards,
Stefi


„Tony_student†ezt írta:

 

[JLatham]

Remember that - most folks have their systems set up to use the , vs ; as the
parameter delimiter in formulas.

Now as to the rest of things, it begins to sound as if we are doing
someone's homework for them? Generally we don't like to do homework for
people - there is more to be learned in the long run if you work out things
for yourself.

Check out the worksheet COUNTIF() function - you'll need one for each
possible hair color. Then you can either use RANK() or MAX() to determine
the most popular.

Actually, a variation of the SUMPRODUCT() formula, with another test for
hair colors would probably work also.