Excel: count number of times a value occurs in a column

Question

Gah, I know this has to be easier than I am making it out to be:

I have a simple spreadsheet of three columns. The first column is the one I am interested in: it contains a value from 00-49. (These numbers indicate the topic under which an item in the third column falls.)

All I want to do is to count the number of times each of these numbers occurs. Right now, I am using this:

=SUM(COUNTIF(Sheet1!A2:A6716, {"00"}))

in a second sheet. It works, but I have found no way to enter the value (00, 01, 02, etc.) without doing it by hand. I thought I would do it clumsily by iterating on a column next to one with this function (essentially starting with 00 and then doing A1+1 on a fill down), but that doesn't work at all -- and I tried several variations.

As you can see, I have quite a few rows here, and I'd like to automate the counting. I tried using a function that built an array, but I couldn't get it to work. Nor could I figure out how to make a pivot table that would do this.

Having entered the first ten (of fifty topics) in the formula above, I know, for instance that I have 638 rows that begin have 00 in the first column, 51 rows of 01, 277 rows of 02, etc.

For reference, the first few rows look like this:

00   0.73,  10.2307/1496207
00   0.69,  10.2307/1496209
00   0.68,  10.2307/1496208

Until you get to this:

49   0.11   10.2307/1499809

For anyone interested, the second column is the proportion of that the topic (the first column) in the document, and the third column is the DOI for each document.

I've tried various solutions from here on StackExchange as well as from Google Search results. Someone please show me the one obvious thing that I have obviously missed in trying to think this through.

Answer 1

I would use:

=COUNTIF(Sheet1!A2:A6716, ROW() - 1)

Adjust ROW() - 1 to match your offset, if you start at row 3 you need to - 2.