Sort a column by data

jon - Jun 27, 2009 at 05:48 PM
 jon - Jun 29, 2009 at 08:45 AM

I had a EXCEL spreadsheet of data for a camp I staff. Over 400 campers. I have a column for t-shirt sizes. I want a formula that tells me how many of each size are in that column. I gave you an example below. I have other columns like this that I need a forumla. I thought if you could give me the formula on this one, I can duplicate.

I want to answer the ? marks below.


Total S = ? (should be 2 smalls)
Total M=? ( should be 2 mediums)
Total L = ? (should be 1 large)
Total XL = ? (should be 2 x-large)

2 responses

Use the countif function. For example, if column A contains the t-shirts sizes your formula would be as follows:


The formula above would be for a small t-shirt. You would duplicate the formula for all of the other sizes.
Thank you. This is what I came up with. Here is an example for my XL shirts: =COUNTIF(F2:F400,"=XL"). I repeated this formula for all sizes.

One other question: (probably another countif function)

I need to capture how many people will be in each class, specifically my 7th and 8th boys and my 7th and 8th girls. These are the 2 classes that are not co-ed. They are gender classes therefore I have to create a formula that combines 2 columns.

I have two columns. One for GENDER and one for GRADE. I have the formula for the 9th grade-Adult. These are co-ed so gender is irrelevant.

The one that is boggling me is when I need to combine 2 columns and count "how many 7th and 8th grade males I have and how many 7th and 8th females I have.

What I am trying to find is the following question marks:

7/8 Females ?
7/8 Males ?
9 4
10 4
11 0
12 1
College 0
Adults 3

Column C Column D
Gender Grade
M Adult
F Adult
F Adult
F 12
F 10
F 9
F 7
M 10
M 9
M 10
M 10
M 9
M 9

Can you help me on this? Maybe copy and paste this into excel and give me the equation. I am stumped! You are my guru-hero. Let me know if you need further info.