Formula for Statistics

[KarinaSilich]

Hi Everyone. I am new here so I hope I am doing the correct thing. I was wondering if someone could please help me with a problem I am having with my spread sheet for work? I will try and explain this as best I can so please stay with me OK I have column A and Column B. Column A has the sex of my employees and Column B has their ages. I require a formula that will add all the male's in column A plus wether they are >17 but <24. I thought the following formula's might work but the first is coming back as 0 and the second is saying #VALUE!...the first formula is =COUNTIFS(K2:L84,"Male",K2:L84,">17",K2:L84,"<24") or the second formula is =DCOUNTA(K2:L84,">17 <25","Male"). I kinda need the information to automatically populate when a new starter starts so my statics are correct but I am getting completely lost and very frustrated. Am I asking to much of Excel? Is it even possible? your help is very much appreciated everyone Karina

 

[Weazel]

maybe something like this ?

Excel 2012

	K	L	M	N
male
male
female
male
male

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFF00"]Sex[/TD]
[TD="bgcolor: #FFFF00"]Age[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]Count[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]N2[/TH]
[TD="align: left"]=COUNTIFS(K2:K6,"male",L2:L6,">17",L2:L6,"<25")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[KarinaSilich]

Oh My Goodness...that worked ))) one last question if I may. Do you have any idea how I am meant to add the number that is in the formula so that it is included in the count? for example =COUNTIFS(K2:K84,"male",L2:L84,">17",L2:L84,"<25") it counts everything that is in between >17 and <25 but it does not count 17 and 25. I really hope that makes some sense. Gosh you are a lifesaver )

 

[AhoyNC]

Just add an equal sign to the formula Weazel gave you. See below:

Excel Workbook
	K	L	M	N
1	Sex	Age		Count
2	male	17		4
3	male	19
4	female	27
5	male	24
6	male	22
Sheet

 

[KarinaSilich]

I do not know how to thank you both ) You have just saved me so much stress ) Thank you so very, very much )