turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 121K Members
- 4,657 Online
- 30.3K Conversations

- Home
- :
- Excel
- :
- Formulas and Functions
- :
- Excel Sum Formula Help

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted
##
##### Excel Sum Formula Help

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-06-2018 12:29 AM

Hi, I'm wondering if there is a function/formula that I can apply to an entire column that will add entities on a row by row basis. I want my cells in column Q, Q5 for example, to give me the sum of N5-O5-P5. I can apply a sum formula for Q5, Q6, Q7, etc. individually, but I would like to know if I can save time by applying a function to the entire column to do this?

The image shows the spreadsheet for clarification.

Thanks

Labels:

5 Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-06-2018 12:58 AM

Kevin,

here is one way to do it:

=SUM(N:N)-SUM(O:O)-SUM(P:P)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-06-2018 01:04 AM

Won't that give me a running total for the entire columns. I am looking to see the sum of the individual rows in the corresponding cell in column Q. Like, in every Q cell, I want to see the corresponding N-O-P sum. Q5=N5-O5-P5 Q6=N6-O6-P6 and so on... without having to create a new formula for every Q cell. Don't know if that makes sense

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-06-2018 02:35 AM

Kevin,

I misunderstood your issue.

Then it is simply:

=N5-O5-P5

And copy the formula down.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-06-2018 04:28 AM - edited 02-06-2018 04:34 AM

Hi, Kevin. I am not sure your question. Your original message said you have done with Sum formula. You can copy and paste in the whole Column Q.

Just in case, perhaps you used something like

=SUM($N$5:$P$5)

In the above formula, the reference address is an absolute reference address. Wherever you copy and paste that formula (except N5 to P5), it gives the same result becuase it refers to the same 3 cells.

You can use relative reference address in Q5 like

=SUM(N5:P5)

If you copy it and paste in Q6, the formula will change to

=SUM(N6:P6)

or copy it and paste to D1, the formula will change to

=SUM(A1:C1)

You may read Switch between relative, absolute, and mixed references for more infomation.

In addition, you may use Fill Handle to fill the formula for you if you use relative reference address for the formula.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-06-2018 07:41 AM

Thank you both! I didn't realize that copying the SUM formula down would change the values to match the corresponding rows. Haven't needed to use excel in a long time.

Thanks again!

Thanks again!

Related Conversations

Conditional Formatting based on Separate Column

Brandon Kennedy
in
Formulas and Functions
on
01-29-2018
104
Views

0 Likes

5 Replies

How to remove today and weekend from start date?

yılmaz sezer
in
Formulas and Functions
on
01-07-2018
47
Views

0 Likes

1 Replies

formula that will return the sum of values with a given name in a given month

Rebecca Schneider
in
Formulas and Functions
on
10-20-2017
431
Views

0 Likes

18 Replies

Excel formula to find a string in column cells and output a value in column cells to the right.

christianvost
in
Formulas and Functions
on
02-15-2018
74
Views

0 Likes

4 Replies

Formula Help for Vlookup and IF

Kim Robin Hicks
in
Formulas and Functions
on
11-16-2017
132
Views

0 Likes

2 Replies

Conditional formatting using formula IF

Noortje Vollenberg
in
Formulas and Functions
on
02-20-2018
57
Views

0 Likes

4 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © 2017 Microsoft