Counting cells within two (or more) columns fulfilling particular criteria

[ashish514]

I have a database with multiple columns named age, sex, occupation etc. I need to make multiple tables from this data on a separate sheet. One example is:


Age Category No. of Males in the category No. of Females in the category
0-3
4-5
6-9
10-15
16-21
22-24

Taking the same example, please tell me how would i do it with a formula, so that i don't have to go through the painful procedure of using filters again and again.

 

[hiker95]

ashish514,

Welcome to the MrExcel forum.


What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples directly in the forum.

Please attach screenshots of your workbook or a sample workbook that accurately portrays your current workbook on one sheet, and what it should look like 'After' on another sheet.

This makes it much easier to see exactly what you want to do, as well as shows us whether there is a consistent number of rows between tables and such.

Here are three possible ways to post small (copyable) screen shots directly in your post:

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

or
RichardSchollar’s beta HTML Maker -...his signature block at the bottom of his post

or
Borders-Copy-Paste



If you are not able to give us screenshots:

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

You can upload your workbook to www.box.net and provide us with a link to your workbook.

 

[ashish514]

hiker95,

My excel version is 2003

Following is the box.net link of the sample data on one sheet and the table required to be made on other sheet.

http://www.box.net/shared/2h69t3hz3l

Actual data has much more rows and columns and there are a no. of tables to be made. But if i get just this example, i can do the rest.

Thanks

 

[ashish514]

I tried this,

=SUMPRODUCT((Range of sex column="1")*(Range of age column="<=3"))

But it is returning 0.

 

[dvananthan]

hi you may try this

=(SUMPRODUCT((Trav!$A$2:$A$28=1)*(Trav!$B$2:$B$28>=(--LEFT(C10,2)))*(Trav!$B$2:$B$28<=(--RIGHT(C10,2)))))/COUNTIF(Trav!$A$2:$A$28,1)*100

regards

ananth

 

[dvananthan]

hi one correction

i wrote the formula in D10 so i use C10

regards

ananth

 

[ashish514]

ananth

Thanks a ton!!!

Actually by mistake i wrote percentage instead of no. of males/females in the columns or output table. But I got it right by deleting the denominator from your formula.

There's another problem, this time there are three criteria, in the same data i need no. of working male and female members in different age categories. The working people are those whose code no. under the occupation column is either <=12 or >=20

I would be highly grateful for the help.

 

[dvananthan]

hi

try this

you may have to do some changes in the range

=(SUMPRODUCT((Trav!$A$2:$A$28=1)*(Trav!$B$2:$B$28>=(--LEFT(C13,2)))*(Trav!$B$2:$B$28<=(--RIGHT(C13,2)*(Trav!$D$2:$D$28>=20)))))+(SUMPRODUCT((Trav!$A$2:$A$28=1)*(Trav!$B$2:$B$28>=(--LEFT(C13,2)))*(Trav!$B$2:$B$28<=(--RIGHT(C13,2)*(Trav!$D$2:$D$28<=12)))))

Othe experts may be able to give a better solution

regards and please confirm.

ananth

 

[ashish514]

Hi Anant,

Thanks a lot again.

You made my work a lot easier.