Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif - Sumproduct
I need to extract data by searching for a numerical value in one column then
a text value from another column. It is a personnel table with ranks listed as numerical values then gender as m or f. I need to know how many of each rank by each gender. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif - Sumproduct
Hi,
Assuming m or f in column A and a rank in column b put this in a cell and drag down for as many ranks as you have =SUMPRODUCT(($A$1:$A$100="M")*($B$1:$B$100=ROW(A1) )) Change M to F to get the female ranks Mike "dave230" wrote: I need to extract data by searching for a numerical value in one column then a text value from another column. It is a personnel table with ranks listed as numerical values then gender as m or f. I need to know how many of each rank by each gender. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif - Sumproduct
Mike,
Column A contains the numerical value for ranks - listed as 1 -10 and colum b for he gender. The problem I am having is finding all how many are rank 1 then how many of each gender "Mike H" wrote: Hi, Assuming m or f in column A and a rank in column b put this in a cell and drag down for as many ranks as you have =SUMPRODUCT(($A$1:$A$100="M")*($B$1:$B$100=ROW(A1) )) Change M to F to get the female ranks Mike "dave230" wrote: I need to extract data by searching for a numerical value in one column then a text value from another column. It is a personnel table with ranks listed as numerical values then gender as m or f. I need to know how many of each rank by each gender. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif - Sumproduct
Hi,
This is a perfect problem for a Pivot Table. Select your data, with titles on the first row, say A1:B1000, choose Data, Pivot Table & Pivot Chart Report, click Next, Next, click Layout and drag the Rank field button from the right to the Row area, then the Gender button to the Column area. Then drag the Gender button to the Data area. Click OK, Finish. If you want a formula approach suppose you enter a list of ranks in cells D2:D11 (numerical values 1 - 10) in cell E1:E2 enter M and F respectively. In cell E2 enter the formula =SUMPRODUCT(--($A$2:$A$1000=$D2),--($B$2:$B$1000=E$1)) Copy this formula down 10 rows and to the right one column. If this helps, please click the Yes button. -- Thanks, Shane Devenshire "dave230" wrote: Mike, Column A contains the numerical value for ranks - listed as 1 -10 and colum b for he gender. The problem I am having is finding all how many are rank 1 then how many of each gender "Mike H" wrote: Hi, Assuming m or f in column A and a rank in column b put this in a cell and drag down for as many ranks as you have =SUMPRODUCT(($A$1:$A$100="M")*($B$1:$B$100=ROW(A1) )) Change M to F to get the female ranks Mike "dave230" wrote: I need to extract data by searching for a numerical value in one column then a text value from another column. It is a personnel table with ranks listed as numerical values then gender as m or f. I need to know how many of each rank by each gender. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT or COUNTIF | Excel Worksheet Functions | |||
Countif or Sumproduct | Excel Worksheet Functions | |||
Sumproduct or countif? | Excel Discussion (Misc queries) | |||
SUMPRODUCT/COUNTIF | Excel Discussion (Misc queries) | |||
sumproduct vs. countif | Excel Discussion (Misc queries) |