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

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

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.