Simple totals

Hi there. 

I'm creating a spreadsheet that displays invoices my business has sent out. 
When they are paid I add the date into a "Paid" column. While they are unpaid 
the cell is left blank. 

What formula would be needed to create automatic totals for paid and unpaid 
invoices?

TIA
0
Utf
12/21/2009 10:47:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
576 Views

Similar Articles

[PageSpeed] 24

Suppose you have column B with dates and ColA with the amount; then try the 
below formulas

'Paid total
=SUMIF(B:B,">0",A:A)

'Unpaid total
=SUM(A:A)-SUMIF(B:B,">0",A:A)
-- 
Jacob


"Sir Arthur" wrote:

> Hi there. 
> 
> I'm creating a spreadsheet that displays invoices my business has sent out. 
> When they are paid I add the date into a "Paid" column. While they are unpaid 
> the cell is left blank. 
> 
> What formula would be needed to create automatic totals for paid and unpaid 
> invoices?
> 
> TIA
0
Utf
12/21/2009 10:53:01 AM
I suggest you to use Sumif Function.

=SUMIF("PAID & UNPAID CELL RANGE","PAID","RANGE OF PAID & UNPAID VALUE")

Examples:-
=SUMIF(A1:A50,"PAID",B1:B50)
=SUMIF(A1:A50,"UNPAID",B1:B50)

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Sir Arthur" wrote:

> Hi there. 
> 
> I'm creating a spreadsheet that displays invoices my business has sent out. 
> When they are paid I add the date into a "Paid" column. While they are unpaid 
> the cell is left blank. 
> 
> What formula would be needed to create automatic totals for paid and unpaid 
> invoices?
> 
> TIA
0
Utf
12/21/2009 10:55:01 AM
Thanks to both of you. All sorted.

"Ms-Exl-Learner" wrote:

> I suggest you to use Sumif Function.
> 
> =SUMIF("PAID & UNPAID CELL RANGE","PAID","RANGE OF PAID & UNPAID VALUE")
> 
> Examples:-
> =SUMIF(A1:A50,"PAID",B1:B50)
> =SUMIF(A1:A50,"UNPAID",B1:B50)
> 
> Remember to Click Yes, if this post helps!
> 
> --------------------
> (Ms-Exl-Learner)
> --------------------
> 
> 
> "Sir Arthur" wrote:
> 
> > Hi there. 
> > 
> > I'm creating a spreadsheet that displays invoices my business has sent out. 
> > When they are paid I add the date into a "Paid" column. While they are unpaid 
> > the cell is left blank. 
> > 
> > What formula would be needed to create automatic totals for paid and unpaid 
> > invoices?
> > 
> > TIA
0
Utf
12/21/2009 10:59:01 AM
In cell AU7 apply the below formula and copy down as required.

=IF(V7=V6,"",SUM(AT7:INDEX(AT7:$AT$100,MATCH(TRUE,INDEX(V7:$V$100<>V7,),)))-INDEX(AT7:$AT$100,MATCH(TRUE,INDEX(V7:$V$100<>V7,),)))

-- 
Jacob


"Sir Arthur" wrote:

> Hi there. 
> 
> I'm creating a spreadsheet that displays invoices my business has sent out. 
> When they are paid I add the date into a "Paid" column. While they are unpaid 
> the cell is left blank. 
> 
> What formula would be needed to create automatic totals for paid and unpaid 
> invoices?
> 
> TIA
0
Utf
12/21/2009 2:52:01 PM
Reply:

Similar Artilces:

Simple graph
I have a chart with zip codes and a population rate. ZIP RATE 80001 2.34 80002 1.23 80003 1.23 I want to make a chart that has the rate on the x axis and the number of times that rate occurs on the y axis. | | | * | | * |_________________________________ | | 1.23 2.34 Thank you. I will assume the ZIP and RATE stuff is in A1:B200 (labels in row 1) Label in G1 to read RATE (but leave it empty for now), label in H2 to read COUNT Make a list of rates in G2:G20 (say) In H2 =COUNTIF($B$2:$B$200,G2) Copy down the row Select H1:G20 and make a...

grouping summary with a total
I have to generate an excel file showing machine capacity by week. our work week is from monday ~friday. with a date field, how do i create weekly totals? I want to show machine hours by machine, week. Are you wanting to generate this file from Access, or strictly within Excel? If strictly within Excel, you'd be best off reposting to a newsgroup related to Excel: this one's for questions about Access, the database product that's part of Office Professional. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "tecas" <ted.castillo@...

why does a formula not total right?
I have say =sum(E1+G3) E1 = $10.00 G3 = 8 The cell I put this formula in comes up (2 or 3) cents off. This does not happen all the time but off & on. http://www.mcgimpsey.com/excel/pennyoff.html -- Kind regards, Niek Otten "Kickstart" <Kickstart@discussions.microsoft.com> wrote in message news:76B67148-9E01-4829-88E8-A777E803D012@microsoft.com... >I have say =sum(E1+G3) > E1 = $10.00 > G3 = 8 > The cell I put this formula in comes up (2 or 3) cents off. > This does not happen all the time but off & on. > Sounds like one of the cells co...

Simple help with implementing Outlook-like GUI
Hi All. I am an experienced unix programmer who sometimes has to do something in the windows world, and always has newbie-questions. Basically I want to write an application program that looks like outlook in that it has nice icons down the left hand side that choose the content of the main area in the right hand side. So I started the MFC App wizard, and got me a SDI program with a CLeftFrame (CTreeView) and a CMainFrame (CFrameWnd). Then I prepared the following snippet of code to draw the buttons: // Create a pushbutton CBitmapButton* pmyButton; pmyButton = new CB...

Simple Simple Simple
Just started Excel (again) and need to make an easy speadsheet fo calculating square inches. Column A: Height Column B: Legnth Column C: Total Tags per 16 x 24 Column D: Cost per tag Figures I know: each sheet cost me $9.98. each sheet is 16 x 24 inches What I want to do is enter the Height, enter the Length and have th total (sum) be entered into columns C and D. I can't believe I forgot how to enter formulas. Any help will b appreciated. Thank you, Crai -- Message posted from http://www.ExcelForum.com Hi Craig, One Way, might be better ways but this'll work: List the colou...

Simple hack to get $500 to your home. 06-05-10
Simple hack to get $500 to your home at http://uknews.tk Due to high security risks,i have hidden the cheque link in an image. in that website on left side below search box, click on image and enter your name and address where you want to receive your cheque.please dont tell to anyone. ...

Totaling a column or row of "x" s
I have several cells with an "x" in them. I am trying to get a total of the "x"s by row and column. I am new at this and need a step by step instruction. Thanks. Try something like this: Count of x's in Col_C =COUNTIF(C:C,"x") Count of x's in Row_4 =COUNTIF(4:4,"x") Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Randi" wrote: > I have several cells with an "x" in them. I am trying to get a total of the > "x"s by row and column. I am new at this and need a step by ste...

calculating longest winning/losing streak and totals
In column A, I have a list of dates and in column B, I have profits and losses in dollar amounts correlating to that date. I want to be able to calculate the longest winning/losing streaks in number of days. I also want to be able to calculate the dollar amount total during that streak Also, while I'm at it, in addition to the function above, I'd like to be able to find the best/worst streak within a defined amount of time. For example, I want to find how much I made in the best 10 day period throughout the year. I'd like the formula to automatically find the most...

Simple Query Wizard hangs
I am using Access 2003 When I initiate the "Simple Query Wizard", I am presented with the first screen - "What fields do you want in your query" When I click on the pulldown list to select a table, I get the following message: "The expression On Get Focus you entered as the event property setting produced the following error: the text you entered isn't an item in the list * the expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]. * There may have been an error evaluating the function, event, or macro&q...

I Need a Simple Sales Software
Hello, I am looking for software to keep track of my prospects, customers, and the notes of the sales process. I spend about one hour a day on sales and marketing, so I prefer a simple and inexpensive software. Maximizer, Outlook, Act, and Goldmine seem made for the full-time professional salesperson. Please let me know if what I am looking for exists. Best Regards, T.I. ...

Make it more simple or intuitive to do simple things
I appreciate the fact that applications are becoming more versatile and able to do things that we hardly thought possible in the past but I feel that in this added complexity you are losing sight of the need to do simple things easily without resorting to trial and error or consulting "help" which often anyhow doesn't lead one straight to the solution! An example is how to produce a chart with a series of months i.e Jan Feb Mar etc appearing on the X axis. This is no doubt something that resulted naturally in the first versions of Excel charts or in a competitor's ea...

Simple public folder permission problem
I have a public task list folder. However even though I have given everyone "Author" priviledges and full control of the directory, users cannot update the tasks (although they can create new ones). The changes to tasks get reset to their original values. Where should I look to fix this problem? Brian How did you grant Author Access, via Outlook or ESM? What do you meanyou gave them full control of the directory? "Brian Taylor" <taylorb@newsgroups.nospam> wrote in message news:e2hwumGrEHA.1160@tk2msftngp13.phx.gbl... > I have a public task list folder. Ho...

What is simple text?
When creating a custom list that includes a column of accounting formated data, I get an error message that 'fields without simple text will be ignored'. The result is all other data except the accounting formatted data. Help!!!! I suppose it means without the currency symbol and thousands delimiters. On Fri, 8 Apr 2005 11:01:04 -0700, TexMas <TexMas@discussions.microsoft.com> wrote: >When creating a custom list that includes a column of accounting formated >data, I get an error message that 'fields without simple text will be >ignored'. The result is ...

How to get total "conditional sum of cells" in a column?
Hi all, I have dollar amounts in one col, and status in another. I want the sum of those dollar amounts where the corresponding status cell is empty (blank). How do I do this? Thanks for any hints, cdj Status in Column A and dollar amounts in Column B: =SUMPRODUCT((A2:A100="")*B2:B100) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk "sherifffruitfly" <sherifffruitfly@gmail.com> wrote in message news:bc08584b-1338-4b3f-8ab3-cc3e1602e581@n1g2000prb.go...

simple question
Okay, I have a very simple question. Where do the old posts go after they run off the last page of this newsgroup? Sometimes I would like to refer to a post that I remember seeing in October, but is no longer on the current section. The search function seems to only search current posts as well. If anyone has some input please email me at joep@siboneylg.com Thanks kindly. have you tried google groups, they keep archived old posts: http://groups.google.co.uk/groups?q=microsoft.public.greatplains&hl=en "Joe Proehl" <joep@siboneylg.com> wrote in message news:099701c5...

Simple Macro is not so simple
Hi all, I am trying to record a macro which will automatically perform the insert > comment function to a selected cell. Should be easy right? Alas no Danny There's sample code on my web site, for inserting comments: http://www.contextures.com/xlcomments03.html Aardvark wrote: > Hi all, > > I am trying to record a macro which will automatically perform the insert > > comment function to a selected cell. Should be easy right? Alas no -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html Wow! Nice one :-) Thank you M...

(Error 1004) Object Defined Error ...simple code
I have the following simple simple code: Dim s as String s = WeekdayName(1) ' Causes Error 1004 Why would the above call to "WeekdayName(1)" suddenly cause an Object Defined Error?? My program has been working nicely for awhile, but now all of a sudden it halts at the above line??? What the heck is going on??? Robert Crandal Hi Robert, It works fine for me. However, checking out Help, the first day of week parameter is supposed to default to Sunday (or 1) as the first day of the week but it appears to default to zero. Tested in xl2002 and x...

Can't see 401(k) contribiltion total
I've set up a 401(k)accont. I transfer contribiltions to this 401(k)account from each paycheck. When I go to the 401 (k) account view, it shows all the informations such as transections and the account vallue ect., EXCEPT the total contribiltion. I want to know not only what my 401(k) vallue is, but how much money I have contribilt total or over a period of time as well. I find no place to see it. Anybody can help? I'm using Money2002 and 2004 Trial Verstion. Thanks a lot! ...

vb--simple simple
hi, anyone know how to copy a combobox selection to a specific cell b using vb code in a command box -- Message posted from http://www.ExcelForum.com i cant see this in the userform. i know you can do this with a normal combo box which is not in a for but lost when in a userform -- Message posted from http://www.ExcelForum.com What's a "command box"? Can you give an clear example of what you want to do? -- Jim Rech Excel MVP "stevieh >" <<stevieh.16v8yw@excelforum-nospam.com> wrote in message news:stevieh.16v8yw@excelforum-nospam.com... | hi, | |...

Formula question -simple for those who know
Hello I'm trying to add a number that was calculated by means of a formula in one column to the sum of a different column. Example: Column A (running balance) Column B (sum) =+e5+c6-d6 =sum(h1:h8) Col A's number is running balance. I copied the formula down so that the next row changes the formula to =+e6=c7-d7 I want that running balance to add to a sum in another column. I can add the first cell to it, but it doesn't change if the running balance changes. I know that is because it's pointing to that single...

How do I sum YTD totals based on monthly totals
I have a financial worksheet with expenses by month and a monthly total for each month. At the end of each subsequent month there is a cumulative total of the monthly totals (YTD), I cannot figure out how to get an automatic formula to populate the correct cells to mimick the previous formulas with the current cell data to get the correct totals. -- Donna EMU Alumni "Bsgrad02" <Bsgrad02@discussions.microsoft.com> wrote in message news:8E3201DF-9B59-4188-8E19-BCC24B00012C@microsoft.com... > I have a financial worksheet with expenses by month and a monthly total for &g...

Totals From Another Worksheet
I want to have a worksheet for data entry, but add and display the totals on another worksheet. How? macro to sheets("destination").range("a2:b6").value=sheets("source").range("a2:b6").v alue or in destination source!a2 -- Don Guillett SalesAid Software donaldb@281.com "DNA" <dnoel@fsgbank.com> wrote in message news:bfc3ab5dc9984a6b90e74f05d029a22c@localhost.talkaboutsoftware.com... > I want to have a worksheet for data entry, but add and display the totals > on another worksheet. > > How? > ...

Pretty simple question
Hello I use Excel 97 in work. I have a frustrating problem. If I have two Excel workbooks open then, in order to switch between them, I have to go to the menu bar and select <Window> and then the appropriate workbook. I am used to Excel 2000 where I can just click the workboook 'icons / tabs' along the bottom of my screen. This is far better. How can I change Excel 97 to allow the open workbooks to display as icons/tabs along the bottom of the screen (as is standard these days). Please help lower my blood pressure... Regards Alex Alex, I don't think that was an ...

Simple Formula Question
hi, I have the following simple formula: =A61*E61 I need to know how I can highlight a block of cells - Let's say row 62-86 and have that formula update itself to reflect each row number (A column & E column remain throughout only the numbers of the row change) Hope that made sense. I know it's a simple proceedure I just can' remember how to do it. Your help would be greatly appreciated as I' really up against a deadline. Thanks!! : -- Message posted from http://www.ExcelForum.com just copy the formula for all rows. Excel will change the formula accordingly Frank ...

A simple question requiring a simple answer
When typing in a cell how do I get the text to drop down a line yet stay in the same cell? ie. In word you hit enter and the cursor drops to the next line. In excel you hit enter and the next cell is selected. -- Pedros ------------------------------------------------------------------------ Pedros's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28202 View this thread: http://www.excelforum.com/showthread.php?threadid=562306 Pedros, use Alt+enter to get a new line in the same cell -- Paul B Always backup your data before trying something new Please post...