1

I have a data table containing integers:

Data table

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.

1

1 Answer 1

1

Use INDEX to set the range:

=COUNTIF(INDEX($A$1:$AAA$5,0,ROW(1:1)),1)

Set the $AAA to the last column with data. Put in the first column and copy/drag down.

enter image description here

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .