Tallying entries by row and column

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Tallying entries by row and column

bill-2
Hi all, new member here but long time gnumeric user and fan!
 
I'm doing a series of spreadsheets with something like 2,000 rows and 50 columns. For each row and column i need to count the number of non-empty cells. I am using the counta function for each of these, but it is quite a long and tedious process to re-input the formula in each column and row. Is there a better formula that would condense the commands to do separate counts for individual rows, placing each total in the appropriate cell? For instance i am putting =counta(G50:DZ50) in F50, =counta(G51:DZ51) in F51, =counta(G52:DZ52) in G52, etc. Can this be combined into one formula that will count non-empty cells for each row and put the total of each in the correct row and cell? Note i don't want to combine totals of multiple rows, just make the counting of each individual row quicker and easier. Many thanks!
 
bill

_______________________________________________
gnumeric-list mailing list
[hidden email]
https://mail.gnome.org/mailman/listinfo/gnumeric-list
Reply | Threaded
Open this post in threaded view
|

Re: Tallying entries by row and column

Morten Welinder-2
> For instance i am putting =counta(G50:DZ50) in F50, =counta(G51:DZ51) in F51, =counta(G52:DZ52) in G52

I assume you meant F52, not G52.

That should be quite easy to enter  Once you have put
=counta(G50:DZ50) into F50, select the cell and press Ctrl-C for copy.
Then select F51 through F100 -- as far as you want -- and press
Ctrl-V.  That's it -- you're done.

Morten
_______________________________________________
gnumeric-list mailing list
[hidden email]
https://mail.gnome.org/mailman/listinfo/gnumeric-list
Reply | Threaded
Open this post in threaded view
|

Re: Tallying entries by row and column

bill-2
Thank you Morten!
 
Yes, i did mean F52, glad my mistake didn't trip you up.
 
I *think* i did as you instructed, but it only copied =counta(G50:DZ50) into F51 without changing the values 50 to 51. Also, it ignored the remainder of the column i had selected.
 
Trying something similar, i did find i can select F50 (the last cell that contains the formula) downward as far as i need, then from the toolbar use Data-Fill-Fill Downwards. This worked perfectly, filling each cell with the formula, corrected for row number.
 
A big thank you as i would not have discovered this without you setting me on the path! MUCH time will now be saved in my projects.
 
peace
bill
 
Sent: Sunday, June 10, 2018 at 6:08 PM
From: "Morten Welinder" <[hidden email]>
To: [hidden email]
Cc: "Gnumeric Mailing List" <[hidden email]>
Subject: Re: Tallying entries by row and column
> For instance i am putting =counta(G50:DZ50) in F50, =counta(G51:DZ51) in F51, =counta(G52:DZ52) in G52

I assume you meant F52, not G52.

That should be quite easy to enter Once you have put
=counta(G50:DZ50) into F50, select the cell and press Ctrl-C for copy.
Then select F51 through F100 -- as far as you want -- and press
Ctrl-V. That's it -- you're done.

Morten

_______________________________________________
gnumeric-list mailing list
[hidden email]
https://mail.gnome.org/mailman/listinfo/gnumeric-list