Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-18-2015, 04:10 AM
Jo Freeman Jo Freeman is offline How to obtain a column total using only specific cells Windows 7 64bit How to obtain a column total using only specific cells Office 2010 64bit
Novice
How to obtain a column total using only specific cells
 
Join Date: Mar 2015
Posts: 19
Jo Freeman is on a distinguished road
Default How to obtain a column total using only specific cells

Hi all, I have a purchase invoice spreadsheet (Invoice details in columns A-G). In columns H & I, I enter the payment details. At the bottom of column D is a total.

I would like this total to only include unpaid invoices. I have entered an IF function +IF(H98>0,0,D98) i.e., if column H has a payment date then don't include this invoice in the total. This works, but I can't see how to replicate it for each invoice without copying it each time I enter a new invoice.

Can anyone help please?



Many thanks
Reply With Quote
  #2  
Old 03-18-2015, 09:24 AM
Snakehips Snakehips is offline How to obtain a column total using only specific cells Windows 8 How to obtain a column total using only specific cells Office 2013
Advanced Beginner
 
Join Date: Mar 2015
Posts: 36
Snakehips is on a distinguished road
Default

Jo,

It sounds to me as if you might be as well converting your Invoice range to an Excel table that will expand as you add data whilst keeping a SUMIF type total row at the bottom.
Here is one of many video tutorials that you will find...

https://www.youtube.com/watch?v=VMScRbwDSXE

Hope that helps.
Reply With Quote
  #3  
Old 03-20-2015, 05:29 AM
Jo Freeman Jo Freeman is offline How to obtain a column total using only specific cells Windows 7 64bit How to obtain a column total using only specific cells Office 2010 64bit
Novice
How to obtain a column total using only specific cells
 
Join Date: Mar 2015
Posts: 19
Jo Freeman is on a distinguished road
Default

Hello Snakehips

Thank you for your reply, but I still can't get it to work. I have converted the spreadsheet to a table and tried the SUMIF formula but when I insert new lines, my total column doesn't update to include the new line.

My original IF formula was =IF(H86>0,0,D86) ie, if H86 contains a figure, don't include the value of D86 in the total. Can you help me with the equivalent SUMIF formula please?

Many thanks
Reply With Quote
  #4  
Old 03-20-2015, 06:18 AM
Jo Freeman Jo Freeman is offline How to obtain a column total using only specific cells Windows 7 64bit How to obtain a column total using only specific cells Office 2010 64bit
Novice
How to obtain a column total using only specific cells
 
Join Date: Mar 2015
Posts: 19
Jo Freeman is on a distinguished road
Default

Hi Snakehips

Please ignore my previous post. I had a typo in my formula. Everything works perfectly now. Thank you so much for yur valuable advice.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Return Sum value of one column from cells not blank in another column zulugandalf Excel 3 08-14-2014 03:37 AM
How to obtain a column total using only specific cells How obtain accurate total of hours estimated of all subtasks that are a certain type? pcumming Project 3 05-14-2014 05:45 PM
How to obtain maximum value of an excel column with a large range? sirkay Excel 4 02-23-2014 08:17 AM
How to obtain the column “C” from column “A” and “B”? Jamal NUMAN Excel 2 02-26-2012 01:28 PM
How to obtain a column total using only specific cells How to count the total cells that contains text data? Learner7 Excel 1 06-30-2011 04:13 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:45 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft