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

Resolved Question:

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.
Submitted: 2 years ago.
Category: Microsoft Office
Expert:  nalosin replied 2 years ago.

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, Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 1578
Experience: 7+ years in troubleshooting and supporting computers, networks, mobile phones and other electronic devices.
nalosin and 3 other Microsoft Office Specialists are ready to help you

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional
 
 
 
Chat Now With A Microsoft Office Technician
nalosin
nalosin
22 Satisfied Customers
7+ years in troubleshooting and supporting computers, networks, mobile phones and other electronic devices.