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, 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

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?

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 4 other Microsoft Office Specialists are ready to help you