1

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.

John Laudun
  • 407
  • 1
  • 9
  • 19

2 Answers2

3

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.

Jesse
  • 1,937
  • 3
  • 19
  • 28
  • That works. I don't understood fully how it works, but it works. – John Laudun Dec 21 '12 at 03:15
  • ROW() returns the row number that the formula is in, rather than hard coding a value you can just refer to the row number. – Jesse Dec 21 '12 at 04:55
  • Of course ROW changes if you delete or add rows above the formula row.....ROWS is more robust, e.g. if first formula is in C2, ROWS(C$2:C2)-1, but even better would be just to list 0 to 49 in adjacent column and then reference that, e.g. with 0 to 49 in B2:B51 just use this formula in C2 copied down =COUNTIF(Sheet1!A$2:A$6716,B2) – barry houdini Dec 21 '12 at 09:57
  • @barryhoudini I agree it would be more transparent but I wouldn't call it better, there are advantages either way. If an additional increment is required I only need to fill your one column down once. With a second column you have to update the series, probably manually. Sometimes a little mystery is good, sometimes transparency is better, depends on the implementation needs. – Jesse Dec 21 '12 at 17:32
  • @Jesse, OK - agreed - perhaps not "better"! That would be my normal approach, but may not fit every scenario..... – barry houdini Dec 21 '12 at 19:32
1

Shouldn't this work?

=COUNTIF(Sheet1!$A$2:$A$6716, 0)
=COUNTIF(Sheet1!$A$2:$A$6716, 1)
=COUNTIF(Sheet1!$A$2:$A$6716, 2)
=COUNTIF(Sheet1!$A$2:$A$6716, 3)

If I am understanding what you need to do correctly, you don't need to use SUM(); COUNTIF() should work, but you'll need to do it once for each unique value that is in your first column. If your first column's values are actually strings rather than numbers, you'll need to do this:

=COUNTIF(Sheet1!$A$2:$A$6716, "00")
=COUNTIF(Sheet1!$A$2:$A$6716, "01")
=COUNTIF(Sheet1!$A$2:$A$6716, "02")
=COUNTIF(Sheet1!$A$2:$A$6716, "03")

until you cover all the values.

Cody Piersall
  • 8,312
  • 2
  • 43
  • 57
  • Thanks for your response, Cody, but I was kind of hoping to avoid this. I'm going with Jesse's solution for now, but I will also play with yours to see what I can learn. – John Laudun Dec 21 '12 at 03:15
  • No problem! It would be better if you put the numbers 0-49 in a different column, as mentioned in a comment in Jesse's answer, and refer to them in your countif formulas. The disadvantage to Jesse's answer is that it is not immediately clear what you are doing, which is especially bad if you want anyone else to know what the spreadsheet does. In my opinion, it is almost always better to be explicit about what you're doing. – Cody Piersall Dec 22 '12 at 21:42