General discussion

Locked

Excel COUNTIF problem

By smnckl1 ·
I have a worksheet with data in several columns. I would like to use the COUNTIF function to extract some numbers. The column headers are, for example, A1 = SEX, B1 = DateOfBirth, C1= Male older than 20, C2 = Female older than 20. The cells A2 to A100 are filled with either "Male" or "Female". The cells B2 to B100 are filled with birthdates entered in the DD.MM.YYYY format.

I would like to put the condition "=COUNTIF(A2:B100, IF(B2:B100>31.12.1980 & A2:A100 = "Male")" to extract the number of males older than 20 years and so on.

Is that possible? I have looked through the help files but it does not show me whether it is possible.

Thanks in advance.

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel COUNTIF problem

by RichTee In reply to Excel COUNTIF problem

The COUNTIF function only takes ONE criteria, of which, you cannot nest another function.

A suggested workaround would be to work with another column (which can be placed off the visible worksheet or hidden). You could use nested ?IF? with ?AND? statements to create specific values and then use the COUNTIF function to count the specific values.

Using the data in your example, in the new column, starting in the X:2 cell:

=If(And(A2=?male?, B2<31-12-1980), ?M20?, If(And(A2=?female?, B2<31-12-1980), ?F20?, ?Under20?))

You can then use the COUNTIF function as

= COUNTIF (X2:X100, ?M20?) to return the number of males over 20

And then use = COUNTIF (X2:X100, ?F20?) to return the number of females over 20

And then use the default value as = COUNTIF(X2:X100, ?Under20?) to count all others

And so forth.

You could continue to up to 7 nesting groups.

Hopes this helps.
Richard

Collapse -

Excel COUNTIF problem

by smnckl1 In reply to Excel COUNTIF problem

The question was auto-closed by TechRepublic

Collapse -

Excel COUNTIF problem

by SanKulPune In reply to Excel COUNTIF problem

Hello smnckl1,

The 'Conditional Sum Wizard' and not 'countif' will solve your problem.

1) If you have not already done, add-in the 'Conditional Sum Wizard' using
Tools > Add-Ins. (See topic 'Install or remove individual components of Microsoft Office or Microsoft Excel' in the on-line help for more info.)
2) Click a cell in the range that contains the data. Let us assume that the data has labels 'Sex' and 'Age' in the top row of the range
3) Click Tools > Wizards and check 'Conditional Sum Wizard'.
4) Follow the instructions in the dialog box that open. Do not bother, at this stage, by the fact that the wizard keeps on referring to 'Sum'.
In the dialog box add the following conditions:

Sex = "Male"
And
Age > 20

After completion of the wizard, you will see a formula in the cell that looks something like this:
{=SUM(IF($B$9:$B$20="Male",IF($C$9:$C$20>20,$C$9:$C$20,0),0))}

a) The curly brackets '{' suggests thet this is an 'array formula'. (For more details, refer to the on-line help on 'array formulas').
b) The range '$B$9:$B$20' refers to the range containing the 'Sex' data.
c) The range '$C$9:$C$20' refers to the range containing the 'Age' data.
d) The result cell, at this stage, contains the sum of ages of males older than 20 years.
5) Enter the edit mode for the result cell and replace the range '$C$9:$C$20' appearing after the >20 clause in the second if clause with 1 (not "1").
(This ensures counting in lieu of the sum.)
6) Now comes the most important part. After editing hit 'Ctrl + Shift + Enter' and not simple 'enter' to ensure that it remains an array formula and looks like this:
{=SUM(IF($B$9:$B$20="Male",IF($C$9:$C$20>20,1,0),0))}

The cell now shows the results you desire.
I have successfully tried this code and would be happy to help you successfully implement this code.

Your feedback would be highly appreciated.


With best regards,

Sanga

Collapse -

Excel COUNTIF problem

by smnckl1 In reply to Excel COUNTIF problem

The question was auto-closed by TechRepublic

Collapse -

Excel COUNTIF problem

by smnckl1 In reply to Excel COUNTIF problem

This question was auto closed due to inactivity

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums