ExcelBanter - Woman age 40

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Woman age 40 (https://www.excelbanter.com/excel-worksheet-functions/234979-woman-age-40-a.html)

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

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

Regards,
Stefi

€žTony_student€ ezt Ã*rta:

A kÃ¶zÃ¶ssÃ©g Ã¼zenetei nem Ã©rhetÅ‘k el

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

"Stefi" wrote:

Community Message Not Available

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

Stefi

€žTony_student€ ezt Ã*rta:

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

"Stefi" wrote:

Community Message Not Available

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" wrote:

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

"Stefi" wrote:

Community Message Not Available

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!

"JLatham" wrote:

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" wrote:

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

"Stefi" wrote:

Community Message Not Available

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" wrote:

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

Stefi

€žTony_student€ ezt Ã*rta:

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

"Stefi" wrote:

Community Message Not Available

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:

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" wrote:

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

Stefi

€žTony_student€ ezt Ã*rta:

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

"Stefi" wrote:

Community Message Not Available

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.

"Tony_student" wrote:

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!

"JLatham" wrote:

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" wrote:

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

"Stefi" wrote:

Community Message Not Available