Results 1 to 9 of 9

Formula for Checkbook Spreadsheet

This is a discussion on Formula for Checkbook Spreadsheet within the Excel Questions forums, part of the Question Forums category; Help with formula. I keep my checkbook on Excel. I am trying to create a sheet to see if my ...

  1. #1
    New Member
    Join Date
    Mar 2009
    Location
    Florida
    Posts
    17

    Red face Formula for Checkbook Spreadsheet

    Help with formula.
    I keep my checkbook on Excel. I am trying to create a sheet to see if my balance agrees with the bank. The sheet that contains the info is named “Checkbook”. On a separate sheet, I want the total of outstanding checks. When a check clears the bank, I put the letter P in Column G, which is beside the amount in Column F. I need a formula that will give me the total amount of checks that have not cleared using the “P” in Column G as the indicator.

  2. #2
    Board Regular repairman615's Avatar
    Join Date
    Dec 2009
    Location
    Tennessee, USA
    Posts
    1,885

    Default Re: Formula for Checkbook Spreadsheet

    Maybe:


    =sumif(coulmn with p in it,"p",chech totals)

    or like this

    =sumif(g1:g100,"p",d1:100)
    ___-Jeff______________

    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Help using VBA Code
    - Helpful links from Hiker95

    Code Tags:
    [CODE] (Place your code here) [/CODE]
    ___-2007-

  3. #3
    New Member
    Join Date
    Mar 2009
    Location
    Florida
    Posts
    17

    Default Re: Formula for Checkbook Spreadsheet

    No, that didn't do it.
    I want the sum of all amounts which do not have the "P" in the column beside it.

    Thanks for the recommendation though.

  4. #4
    Board Regular
    Join Date
    Apr 2009
    Posts
    1,037

    Default Re: Formula for Checkbook Spreadsheet

    On your "separate sheet"

    =SUMIF(Checkbook!G2:G7,"",Checkbook!F2:F7)

    That is provided the cells in G are blank if not cleared.

    Show your sheet on the board, get MrExcel HTML Maker or Excel Jeanie
    Search MrExcel through Google Here

    Using Excel 2007 on Windows 7

  5. #5
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,262

    Default Re: Formula for Checkbook Spreadsheet

    Welcome to the Board!

    One word: Quicken

    Why try to recreate the wheel?
    Smitty

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

  6. #6
    Board Regular repairman615's Avatar
    Join Date
    Dec 2009
    Location
    Tennessee, USA
    Posts
    1,885

    Default Re: Formula for Checkbook Spreadsheet

    Quote Originally Posted by Smitty View Post
    Welcome to the Board!

    One word: Quicken

    Why try to recreate the wheel?

    I just want to ride on it for a while!


    Quote Originally Posted by whitaker View Post
    No, that didn't do it.
    I want the sum of all amounts which do not have the "P" in the column beside it.

    Thanks for the recommendation though.

    =sumif(g1:g100,"<>p",d1:d100)
    I think this is how to express does not equal "p".
    ___-Jeff______________

    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Help using VBA Code
    - Helpful links from Hiker95

    Code Tags:
    [CODE] (Place your code here) [/CODE]
    ___-2007-

  7. #7
    Board Regular
    Join Date
    Apr 2009
    Posts
    1,037

    Default Re: Formula for Checkbook Spreadsheet

    Did you try the formula I posted above?
    Show your sheet on the board, get MrExcel HTML Maker or Excel Jeanie
    Search MrExcel through Google Here

    Using Excel 2007 on Windows 7

  8. #8
    Board Regular
    Join Date
    Apr 2009
    Posts
    1,037

    Default Re: Formula for Checkbook Spreadsheet

    My formula was based on your "requirement"

    On a separate sheet...
    So the formula offered would be placed in a cell on your "separate sheet"
    Show your sheet on the board, get MrExcel HTML Maker or Excel Jeanie
    Search MrExcel through Google Here

    Using Excel 2007 on Windows 7

  9. #9
    New Member
    Join Date
    Mar 2009
    Location
    Florida
    Posts
    17

    Default Re: Formula for Checkbook Spreadsheet

    I'm sorry it took me so long to get back to this, but I got side tracked. Yes, the formula worked like a charm. I just changed the row range to accomodate the specfic year I wanted to look at. Smitty (Moderator)mentioned Quicken. I use Excel extensively at work and have to write my own formulas to obtain the analysis I want. I want to understand formulas and their applications. The more I know about what can be done with formulas, the more capable I am to resolve data problems at work. I'm slowly learning, and quite pleased with the things I can do with formulas. However, I realize I have not scratched the surface. Thank you so much for taking the time to respond to the formula question.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com