Find out most common with variable agegroup

Here is a array entered formula* option:

Formula:

Please Login or Register  to view this content.

To get the most common by gender (female in this case) add another IF function as follows:

Formula:

Please Login or Register  to view this content.

*Array entered formulas are activated by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Formulas are applied in ranges next to the summation tables at the bottom of the spreadsheet.
Let us know if you have any questions.

Or simply using COUNTIFS. Formulae like this one:

=IF($AE647="","",COUNTIFS($R$2:$R$637,$AE647,$I$2:$I$637,">="&$AF$644,$I$2:$I$637,"<"&$AF$645))

See sheet for context. Incidentally, you might find it handier to do all your counting on another sheet, or at the top of an empty column, then you can more readily use whole-column references which can make the formulae easier to set up. It also means that the results dont have to be moved around if/when the data change.

Sealpino - your query is well beyond that of a basic user. I am moving your thread to a more appropriate section.

Who are you "talking" to???

Glenn - who are YOU "talking" to? When threads are out of sequence, it's appropriate to post part of the post to which you are responding. I presume you are talking the the OP and not me, since I addressed the OP directly in post #6?

Originally Posted by sealpino

According to Ali, the question is moved to another section. Sorry for not knowing where is the limit of the questions to be made.

Yes, I moved your thread to the appropriate section - Excel Formulas & Functions. This is probably where most of your future queries should be placed, as they are not very basic Excel questions.

to return the names in Rank Order needs this array formula:

=IFERROR(INDEX($R$2:$R$637,MODE(IF($R$2:$R$637<>"",IF(COUNTIF(AE$646:AE646,$R$2:$R$637)=0,MATCH($R$2:$R$637,$R$2:$R$637,0)+{0,0})))),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

EDIT: incorrect sheet attached originally...

Originally Posted by AliGW

Glenn - who are YOU "talking" to? When threads are out of sequence, it's appropriate to post part of the post to which you are responding. I presume you are talking the the OP and not me, since I addressed the OP directly in post #6?

Yes. I was talking to Sealpino

It works really fine the way you made it.
What I see is that the names in the R column are listed in top list regardless the total asked within the age group.
Could the names follow the column with the results of the total?


From 1900
To 2017
Names Total Female Male
holly 120 69 51
grotta 103 103 0
bravo 74 74 0
terra 34 34 0
cargo 25 15 10
kollo 14 14 0 This line should be the last one in the list?
delta 17 17 0
alfa 16 16 0

Total 403 342 61

Fixed... (I hope). I forgot to add a date between X and Y to the MODE.