Lookup/ Countif / Sumif Query

**prfellows** · 03-03-2013, 10:42 PM

I have two columns with data that I need to count based on two main criteria. First column is either M or F. The second column has numbers in it ranging from 20 to 38 (by 2s). I need to count the number of "F"s that have a 20, 22, 24, etc. for ordering purposes. Each column has 128 rows so I don't want to do this by hand. (The first column is gender and the second is swim suit size for our swim team.)

Example data:
F 22
F 28
M 32
M 24
F 26
M 22
F 32
M 36
F 24

I need a summary for EACH M and F to look something like this:
Size Count
20 6
22 8
24 10
26 17
28 12
30 15
32 9
34 4
36 7

I tried count(if(...))...) but couldn't quite get it to work. I tried just countif, but that doesn't take into account the M/F criteria.

Any thoughts?????

(It's ridiculous that I can't figure this out for myself tonight!)

Thanks in advance.

**benishiryo** · 03-03-2013, 10:55 PM

hi prfellows, welcome to the forum. assuming your data is in A2:B150, then try this somewhere:
=SUMPRODUCT(($A$2:$A$150="F")*($B$2:$B$150=22))
or in Excel 2007 & above:
=COUNTIFS($A$2:$A$150,"F",$B$2:$B$150,22)

you can even change those in red to refer to your table. assuming your table of size starts from F2:
=SUMPRODUCT(($A$2:$A$150="F")*($B$2:$B$150=F2))
=COUNTIFS($A$2:$A$150,"F",$B$2:$B$150,F2)