How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask nalosin Your Own Question

nalosin
nalosin, Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 1578
Experience:  7+ years in troubleshooting and supporting computers, networks, mobile phones and other electronic devices.
31004542
Type Your Microsoft Office Question Here...
nalosin is online now
A new question is answered every 9 seconds

I use Excel in Office for Mac 2011... I have no problem summing

This answer was rated:

I use Excel in Office for Mac 2011... I have no problem summing a number of columns to a single column.

However, I work with worksheets that have a number of rows, ranging from 6,000 to 45,000, and dragging the lower right-hand of a cell down the column to obtain the sums is awful tedious... Is there a way to make this a lot faster?

Thanks.

nalosin :

Hello


 

nalosin :

and welcome to JustAnswer.com


 

nalosin :

I will do my best to help


 

nalosin :

The easiest way to sum column is to type the formula directly in the cell


 

nalosin :

for example:


 

nalosin :

=SUM(A1:A5000)


nalosin :

A1 is the starting cell and A5000 is the last cell you want to include in SUM


 

nalosin :

Do you wish to SUM complete row maybe?


 

nalosin :

Let me know whatever you need, I can help


 

Customer:

I'm not sure I understand...


 


For example... I can sum cells A1:D1 in E1..


 


Now I want to sum cells A2:D2 in E2 and so on... so how do I get through to E6000

nalosin :

Ok. In the text field that is on top of all cells just before the formula field


 

nalosin :

just enter E6000


 

Customer:

No. I want to Sum each of the rows down the column.

nalosin :

then in formula text field type the formula command


 

nalosin :

Ok. If you have more rows that you want to sum


 

nalosin :

then when you for example put all A2:D2 to sum in E2


 

nalosin :

now if you select E2


 

Customer:

Help, are you there?

nalosin :

and put a mouse in bottom-right corner of the cell you will get + instead of your regular cursor


 

nalosin :

then just click when + shows up and scroll down E3, E4, E5


 

nalosin :

and it will consequently sum A3:D3, A4:D4, A5:D5


 

nalosin :

I don't know if you understood well, but please try and let me know if this is what you want to achive


 

Customer:

After I Sum a number of columns in a row, the rows that are below the first row also has data in each of the columns...


 


How do I Sum each row (say, for 6000 rows) without having to drag the cursor down the entire column?


 


Can I insert a formula someplace to SUM each row in the range?


 


 


 


 


 

nalosin :

Yes, you can


 

nalosin :

please try all I wrote above


 

nalosin :

sum one row


 

nalosin :

then select the cell where SUM formula is


 

nalosin :

then put mouse cursor to the bottom-right of that cell and + sign will show


 

nalosin :

after you see + press left click on mouse and scroll down


 

Customer:

Hold on... and let me try.


 


Help, I don't want to scroll down... you try scrolling down 6000 rows, and it takes forever.

nalosin :

yes, but you just have to do this once. It's the easier way


 

nalosin :

you don't have to scroll through each row


 

nalosin :

it will automatically sum complete row, and you have to scroll just through one column


 

Customer:

I have one worksheet that has 50,000(!) rows... to scroll down that many takes a really long time... That's why I came to you folks.

nalosin :

Ok. Hold on


 

nalosin :

I will try to find the simpler solution


 

Customer:

Good.

Customer:

FYI... I've made up a very simple spread sheet, so I can try out what you suggest.

nalosin :

Ok.


 

nalosin :

The other way is to use the Macros and write a script that will do what you want to achive


 

Customer:

How do I do that?

nalosin :

Hold on just a minuet


 

nalosin :

*minute


 

Customer:

I have entries in Row 1: Columns A, B, C and E and have Summed them in Column E.


 


Now I also have data in Rows 2, 3, 4, 5 AND 6, in Columns A:D and want to sum each of the rows in Column E without having to drag

nalosin :

Ok. Select cell E in row 1


 

nalosin :

then put mouse (don't click) in bottom-right edge of the selected cell


 

nalosin :

then when + shows click and drag down


 

Customer:

Done...


 


Help, is my E1 supposed to be empty or does it have the formula?

nalosin :

you have to SUM just for this E1 cell


 

nalosin :

then when you put mouse to + and drag down it will SUM from it self for all other rows


 

nalosin :

Ok. I found even more simpler solution


 

Customer:

I know how to do that... but it'll take forever to do 50,000 rows.

nalosin :

Please use SUM formula for E1 cell


 

nalosin :

then select E1 cell


 

nalosin :

click CTRL+SHIFT+DOWN ARROW


 

nalosin :

then press CTRL+D


 

nalosin :

Please try this


 

Customer:

Help... I pushed down simultaneously on CTRL+SHIFT+DOWN ARROW, the released and pushed CTRL+D... Nothing happened.

nalosin :

Ok. Did you select E1 cell first


 

nalosin :

after you press CTRL+SHIFT+DOWN arrow on keyboard all cells bellow E1 should be selected


 

Customer:

Yes, I started with the cursor in E1

nalosin :

Note that all cells bellow E1 must be blank


 

Customer:

I tried with the cursor in the cell E0 and it showed the formula and highlighted the entire column... I think we're getting closer.


 


They are.

nalosin :

Ok. Great


 

nalosin :

then just if you sum to E1 cell


 

nalosin :

after you press CTRL+SHIFT+DOWN arrow it will select complete column bellow E1 cell


 

nalosin :

then when you press CTRL+D it will


 

nalosin :

put sum formula to E2, E3, E4 and all other cells bellow


 

Customer:

Nothing happens when I press Control+Shift+Down arrow

nalosin :

You said above that it highlighted the whole column


 

nalosin :

Note once again that all bellow columns must be blank


 

Customer:

No. the entire column was highlighted when I went to E0


 


All rows in column E below row 1 are blank..


 


I am working with a Mac... anything to this?

nalosin :

Can you try all of these in a new spreadsheet


 

nalosin :

just fill out A, B, C column


 

nalosin :

rows 1, 2 3


 

nalosin :

then in D1


sum A1, B1, C1

nalosin :

then select D1 and press CTRL+SHIFT+DOWN arrow and let me know if it selects all bellow cells


 

Customer:

yes... and nothing is happening

nalosin :

that's strange


 

nalosin :

if you press CTRL+SHIFT+RIGHT arrow


 

Customer:

Hey, Command+Shift+Down arrow highlights the column

nalosin :

Ok. Great


 

Customer:

Command+Shift+Down arrow, then Control D works.

nalosin :

then press Command+D to fill all bellow with SUM formula


 

nalosin :

Ok.


 

nalosin :

It's a little bit different as some keyboards doesn't have Command button, just CTRL


 

Customer:

I am happy, since this now works!!!


 


Thanks.

nalosin :

Ok. Great. I am glad that we solved it


 

nalosin :

eventually.


 

nalosin :

Thank you very much for using JustAnswer and have a great day


 

nalosin :

Please don't forget to rate my answer if you were satisfied


 

Customer:

One more question... the rest of the

Customer:

One more question, the rest of the rows have "0" how do I empty the rows with no data?

nalosin :

HOld on just a minuet


 

Customer:

As you can guess, I now have the opposite problem, I have a "0" in the remaining 50,000 or so rows.


 


I'll hold on a "minute." I don't dance the 'minuet' I think I can joke with you.

nalosin :

:-)


 

nalosin :

I understand, there is a solution to that problem too


 

Customer:

I could "cut/copy and paste" the good data to another worksheet, but then I'd have to highlight and scroll down... not good

nalosin :

I know. We have to change the formula


 

Customer:

Okay. Let's do it.

nalosin :

Ok. Formula is


 

nalosin :

=IF(COUNTA(A1:D1),SUM(A1:D1), "")


nalosin :

if you put the formula in E1


 

nalosin :

then if there is no data in A1, B1 or D1 it will show null in E1


 

nalosin :

it will not show zero


 

Customer:

hold on please... NOPE.

nalosin :

I tried on my sheet


 

nalosin :

it's working


 

Customer:

Help, I had only 3 (A,B,C) columns of data... I did the Sum macro in D1, AND I TRIED THE FORMULA IN E1 AND GOT AN ERROR MESSAGE. I TOOK THE FOMULA THAT WAS SUGGESTED BY EXCEL AND GOT A "VALUE" IN E1.

nalosin :

Ok.


 

nalosin :

Hold on, I will change it for D1 field


 

nalosin :

=IF(COUNTA(A1:C1),SUM(A1:C1), "")


nalosin :

put this in D1


 

Customer:

Help, am I supposed to do the Command+Shift+Down arrow, the Control D first?

nalosin :

No. First put the formula in D1


 

nalosin :

then do Command+Shift...


 

nalosin :

Please use this formula above instead of regular SUM formula


 

nalosin :

just copy/paste it to D1 cell


 

Customer:

Okay, I got it to work... after you enter the fancy formual... you then do the Command+Shift+Down arrow, then Control D.

Customer:

Please let me write this down...

Customer:

Okay... I think I have it...


 


Thanks.

nalosin and other Microsoft Office Specialists are ready to help you

Related Microsoft Office Questions