CODEWITHSUNDEEP
excelvbaexcel-formula
nischalinn
nischalinn

Reputation: 1175

counting of gender based upon age distribution

I have an excel sheet of data. I have to prepare report based on gender and age wise distribution count. The excel sheet has three columns NAME, GENDER, AGE. I have to get the count of Male, Female Population according to age distribution of 

Under 10 
10 yrs - 18 yrs     
19 yrs - 49 yrs     
50 yrs - 59 yrs     
Above 60 yrs

How can I get the count in excel? I have no idea of excel programming. Attached herewith is only sample data I have thousands of rows of actual data. I have no idea how to do such analysis in excel. Please help!!! I've attached the data as well as required count format in the image.

enter image description here

Upvotes: 0

Views: 6106

Answers (2)

Tim Williams
Tim Williams

Reputation: 166725

Here's what I meant:

enter image description here

Binned Age is from the table in H:I using index/match formula. Pivot table uses BinnedAge then Gender for columns and count of name for values

If you want the bins sorted in a different order in the pivot table then see: https://www.excel-university.com/sort-a-pivottable-with-a-custom-list/

Upvotes: 0

Variatus
Variatus

Reputation: 14383

@Ted Williams, I didn't find a solution as you suggested. I failed to get rows or columns for each of the five age groups into the pivot table. So, I guess the task you set for OP was too difficult. Instead of a pivot table I used the formula below to assign each age to one of the five age groups.

[D2] =MATCH(C2,{0,10,19,50,60},1)

Next, I built a copy of your "Reporting Format" table in A17:K18 - five age groups with 2 columns each, just as you published it. Then I entered the next formula in B20 and copied all the way across to K20.

[B20] =COUNTIFS($B$3:$B$15,B$18,$D$3:$D$15,INT(COLUMN()/2))

Column D of the table this formula references contains the age groups already mentioned above and column B contains the gender. This table was copied form the data table you posted, assuming that the names are in column A.

INT(COLUMN()/2) is a counter which is built on the the original formula being in column B. COLUMN() returns the number of the column in which the formula resides. If the original is in column B it will return 2, 3, 4, 5, 6, 7, 8, 9, 10 as it is copied to B20:K20. Dividing this number into 2 will create a fraction which we can't use to address column numbers but the INT() function will round that fraction down. So, the result of =INT(COLUMN()/2) will be 1, 1, 2, 2, 3, 3, 4, 4, 5, 5, exactly the age groups we have in column D and, of course (hopefully) the age group captions we have in row 17. The point of this explanation is that this function may return the wrong result if the original is not in column B. Adjust it like this, =INT((COLUMN()+x)/2) (where x is likely to be a negative number) and test it by itself in the columns in question to ensure that it returns the numbers 1 to 5, each number twice.

Upvotes: 2

Related Questions