0

I have a large amount of data set up in the form of rounds. I need to count down the 'Round' column while counting if '1' occurs in the 'Prediction' column. I know how to do this manually but I have over 200 rounds I need to account for. I'd like to just drag across COUNTIFS(C2:C11, 1) but Excel assumes I want the same cells in the next row (D2:D11) instead of the next group down the column (C12:C21). I'd like to a way to tell it to do something like COUNTIF(x+1:y+1, 1), but down a column.

sample of data

raine
  • 29
  • 8
  • Instead of a formula you could use a pivot table and pivot on Round and use Count of Predictions as the value. – Jerry Jeremiah Feb 15 '16 at 00:18
  • Count of Predictions only seems to give me the total value of the predictions in each round, is there a way I can pivot on Round and get it to give me just the amount of occurrences of '1'? – raine Feb 15 '16 at 01:56
  • when you make a pivot table there are many ways to aggregate the data. In your case put the Predictions in the column headers as well as the table values and change the table values from sum to count. You can get to that choice by right clicking on the table values and choosing field properties. – Jerry Jeremiah Feb 15 '16 at 09:53

2 Answers2

1

Try this:

=COUNTIFS($C:$C,1,$A:$A,COLUMN(A:A))

As you drag to the right the round number represented by COLUMN(A:A) will go from 1 to 2 to 3.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Could you possibly give me an example of what I should enter? I initially did COUNTIFS(C2:C12,1,A2:A12,COLUMN(A:A)) and it didn't count correctly. – raine Feb 15 '16 at 01:21
  • 1
    Scott - see the comment attached to my response. I think he/she wants to drag down now, not across. Personally, I'll wait until they can figure out what they actually want. –  Feb 15 '16 at 02:11
  • You would enter it as I typed it with the full column references in G1 and drag to the right. – Scott Craner Feb 15 '16 at 02:13
1

I need to account for. I'd like to just drag across COUNTIFS(C2:C11, 1) but Excel assumes I want the same cells in the next row (D2:D11) instead of the next group down the column (C12:C21).

The formula should reference ten vertical cells and shift to the group of ten vertical cells for every column it is dragged right.

The INDEX function will work well for this and at hte same time, avoid the use of volatile functions like OFFSET. Calculate the rows with the COLUMN function providing the increment (adjusted by a little maths).

=COUNTIF(INDEX($C:$C, 2+(COLUMN(A:A)-1)*10):INDEX($C:$C, 11+(COLUMN(A:A)-1)*10), 1)

Starts at C2:C11. Drag right for C12:C21, C22:C31, etc.

The INDEX function does more than return a value; it actually returns a valid cell address. When used in its most common lookup capacity, you see the value returned but in reality, unlike a similar VLOOKUP operation which only return the cell's value, INDEX is returning the actual cell; e.g. =A1, not the 99 that A1 contains. This hyper-functionality can be used to create valid ranges that can be used in other functions. e.g. A2:A9 can also be written as INDEX(A:A, 2):INDEX(A:A, 9).

  • This counted correctly but when I dragged down it only changed the range by 1 cell instead of 10. Could you give me an example of how to start? I'm not sure where I should be explicitly defining the cells in the function, do I state I want C2:C12 in both index functions? – raine Feb 15 '16 at 01:25
  • You are going to have to make up your mind whether you want to *'*drag across** (from the question) or **'dragged down'** (from your recent comment). If you want to **'drag down'** and shift by 10 cells then exchange ROWS for COLUMNS. I'm pretty sure any **[enthusiast programmer](http://stackoverflow.com/tour)*'* should be able to do that. –  Feb 15 '16 at 02:09
  • That wasn't really the focus of my comment but I understand. Thanks for the help. – raine Feb 15 '16 at 02:17