Constraing a count using two columns. . .

[cmdoebler]

I have two columns. One is just years (1997, 1998, 1999, or
2000). The second column is (1, 2, 3, or 4). I have to count eac
number by year. Seems extremely simple. I am new to excel but I ca
not find anything to help me here.

Thank you,

Chri

 

[duane]

if you want to add all the numbers for a given year use sumif(yea
range,year you want,other range). not sure if that is what you wan
though....

 

[cmdoebler]

It actaully has to do with grades. The table is sorted by student id
Column 1 has the student year, obviousily random due to the sort that
do not want to change. Column 2 has grades (0, 1,2,3,or 4) als
random. I need to count each grade for each year. So there are 4 (3)
in 1997 and 6 (3)s in 1999. I am looking for the function to do this

 

[duane]

i don't know a function but a macro could separate the data int
groupings by year and then you could do a countif on each grouping fo
each grade or...
you could copy the data to another sheet, sort both by year (primary)
and then by grade. Then, assuming the year is in C6, C7 etc and th
grade in D6, D7 etc...

=IF(C6=C5,IF(D6=D5,E5+1,1),1) in E6 gives a running count of grade b
year

=IF(AND(C7=C6,D7=D6)=TRUE,"",E6) in I6

=IF(I6="","",D6) in H6

=IF(I6="","",C6) in G6

results in listing of year (or a blank) in column G, grade or a blank
in column H, and # occurrance (or a blank) in column I

leaving you with a unique year/grade/count listin

 

[duane]

actually here's a better way now that I think....insert a new column sa
D7 and enter =B7&C7 assuming B7 is the year and C7 is the grade - thi
gives you a unique combo of year&grade to use as the criteria in
countif

 

[Biff]

Hi cmdoebler!

It all depends on how your table is setup.

If your table has years in col A and grades in cols B:F,
you can use something like:

=COUNTIF(B2:F2,0)

If your table has random years in col A and grades in col
B, you can use something like:

=SUMPRODUCT(--(A2:A100=2000),--(B2:B100=0))

Biff

 

[Frank Kabel]

Hi Chris
in addition to the formula solutions you have received you may also
consider using a pivot table for this. See:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm

 

[Domenic]

Hi Chris,

Here's another approach you may want to consider.

Assuming the years are in Column A, and the grades in Column B...

1) Label your column headings 0, 1, 2, 3, and 4 in Row 1, starting a
E1

2) Label your row headings 1997, 1998, etc., in Column D, starting a
D2

3) Put the following formula in E2, copy across and down:
=SUMPRODUCT(($A$1:$A$100=$D2)*($B$1:$B$100=E$1))

Now you'll have a table where you can look down Column D for the yea
in question and then look across to the column under the appropriat
grade to come up with the number you want.

Hope this helps