Counting numbers in a very large poll.

Closed
Report
Posts
7
Registration date
Saturday March 27, 2010
Status
Member
Last seen
May 12, 2010
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
I have a large workbook with numbers that range from 1 to 70. In the example I have attached, from column A to column AV.(I could have as many as 70 columns and 6000 rows of data.)

My task is to count every number from 1 to 70 in each row and input the total of each number into the corresponding number in the section starting at AY. The original numbers are separated by 2 columns from the answers.
Column AY has all the 1's in each row, column AZ has all the 2's in each row..and so on and so forth.

In the example attached, I used the COUNTIF function for illustration but it is so tedious to use that for a large ammount of data.

Can someone help me compose a macro that could do that.

http://wikisend.com/download/602736/COUNT.xlsx

Thank you in advance.

Rhoknee

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
The numbers will stay same? By that I mean that every time you would have 1-70 numbers (all numbers) and you simple want count of those number for each row ?

If that is the case

you could modify your countif formula

as
=COUNTIF(G3:AV3,"=" &$AY2)

then you can drag the formula