I have a data table containing integers:
I need to evaluate the numbers of times a 1 appears in a column and the results need to be in presented in a column vertically. Thus for column A the count = 0, for column B = 1, for column C = 2 etc and should be presented as follows;
0
1
2
3 ...etc
Therefore the column reference is incremented by one (A > B > C >D etc).
Using the formula countif, I can manually do this for a single column. My problem is that I have hundreds of columns and need to copy the formula vertically downwards whilst maintaining the row reference, but incrementing the columns reference by one.
I have a tried a combination of Countif() and Offset() but to no avail.
=COUNTIF(OFFSET(A2;0;0;5;1);1)
This produces the correct result for one column, but as I copy downwards, the column "A"stays the same. This need to be as follows:
=COUNTIF(OFFSET(A1;0;0;5;1);1)
=COUNTIF(OFFSET(B1;0;0;5;1);1)
=COUNTIF(OFFSET(C1;0;0;5;1);1) etc
If anyone can help. I would be grateful.