Countif - Sumproduct

[dave230]

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.

 

[Mike H]

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]

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

 

[ShaneDevenshire]

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
D211 (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.