Results 1 to 8 of 8
  1. #1
    Members
    Join Date
    Nov 2015
    Location
    Sittingbourne, Kent
    Posts
    2,419

    A Question for Excel Whizzkids


    0 Not allowed!
    Can find my way round Excel for the most part, at an intermediate level, but am currently stumped by a dilemma.

    I have a spreadsheet that has multiple worksheets on, each for a different financial account - what I would like to know is there a way to find a total value for all the accounts from each different spreadsheet combined, on a set date?

    Just to further add confusion though, each account doesn't have an entry for every day - so I am guessing some sort of date range would need to be applied!

    Any help greatfully received.

    • North Stand Chat

      advertising
      Join Date: Jul 2003
      Posts: Lots

        


    • #2
      Members Stat Brother's Avatar
      Join Date
      Jul 2003
      Location
      West west west Sussex
      Posts
      42,447


      1 Not allowed!
      Quote Originally Posted by darkwolf666 View Post
      This quote is hidden because you are ignoring this member. Show Quote
      Can find my way round Excel for the most part, at an intermediate level, but am currently stumped by a dilemma.

      I have a spreadsheet that has multiple worksheets on, each for a different financial account - what I would like to know is there a way to find a total value for all the accounts from each different spreadsheet combined, on a set date?

      Just to further add confusion though, each account doesn't have an entry for every day - so I am guessing some sort of date range would need to be applied!

      Any help greatfully received.
      Just wait 3 months.
      “Melancholy is incompatible with bicycling.” James E Starrs
      http://app.strava.com/clubs/north-stand-geeks
      Fantasy Vuelta - League Name: NSC Geeks League Code: 68204709

      .
    • #3
      Members cloud's Avatar
      Join Date
      Jun 2011
      Location
      Here, there and everywhere
      Posts
      2,719


      0 Not allowed!
      Create a blank tab (new / worksheet)
      Copy and paste the row and column headers from one of the existing sheets into the new one.
      In the first cell where you want a combined value, click in the cell and type =
      now click the equivalent number from the first set of data
      then add +
      then click the next one you want to add.
      When you're done just press enter. The total will now be in the cell and you can see the formula up at the top.
      To copy this to the rest of the cells and rows, just copy and paste right and down.
      This should work unless each tab is arranged differently, but you can still check each individual formula to make sure it is referencing the right cells.
    • #4
      Members
      Join Date
      Nov 2015
      Location
      Sittingbourne, Kent
      Posts
      2,419


      0 Not allowed!
      Quote Originally Posted by cloud View Post
      This quote is hidden because you are ignoring this member. Show Quote
      Create a blank tab (new / worksheet)
      Copy and paste the row and column headers from one of the existing sheets into the new one.
      In the first cell where you want a combined value, click in the cell and type =
      now click the equivalent number from the first set of data
      then add +
      then click the next one you want to add.
      When you're done just press enter. The total will now be in the cell and you can see the formula up at the top.
      To copy this to the rest of the cells and rows, just copy and paste right and down.
      This should work unless each tab is arranged differently, but you can still check each individual formula to make sure it is referencing the right cells.
      Thanks for the reply, unfortunately that is what I am already doing, but that is cell/row specific and not, if I am understanding you correctly, date specific!

      Thanks any way...
    • #5
      Members KZNSeagull's Avatar
      Join Date
      Nov 2007
      Location
      Wolsingham, County Durham
      Posts
      12,290


      0 Not allowed!
      Sumifs?
      Sanibona!!
    • #6
      Members KZNSeagull's Avatar
      Join Date
      Nov 2007
      Location
      Wolsingham, County Durham
      Posts
      12,290


      0 Not allowed!
      This will add up values based upon a date range on one sheet:

      =SUMIFS(B2:B8,A2:A8,">="&E2,A2 :A8,"<="&E3)

      Where B2:B8 are the values to add up, A2:A8 is the list of dates and E2 and E3 are the start and end dates you want to find. It works, just tried it. Will now try to work out how to sum up multiple values across multiple sheets. I may be some time......

      Make sure that the dates are in the same format by the way.

      Edit: To keep things simple (because at some point you may want to change things and get utterly confused by some ridiculously convoluted formula in one cell), I would do the above for each sheet and then add up those totals seperately to get your total. Intermediate working sub totals and one total from those essentially.
      Last edited by KZNSeagull; 10-06-2018 at 14:57.
      Sanibona!!
    • #7
      Members
      Join Date
      Nov 2015
      Location
      Sittingbourne, Kent
      Posts
      2,419


      0 Not allowed!
      Quote Originally Posted by KZNSeagull View Post
      This quote is hidden because you are ignoring this member. Show Quote
      This will add up values based upon a date range on one sheet:

      =SUMIFS(B2:B8,A2:A8,">="&E2,A2 :A8,"<="&E3)

      Where B2:B8 are the values to add up, A2:A8 is the list of dates and E2 and E3 are the start and end dates you want to find. It works, just tried it. Will now try to work out how to sum up multiple values across multiple sheets. I may be some time......

      Make sure that the dates are in the same format by the way.

      Edit: To keep things simple (because at some point you may want to change things and get utterly confused by some ridiculously convoluted formula in one cell), I would do the above for each sheet and then add up those totals seperately to get your total. Intermediate working sub totals and one total from those essentially.
      Will give that a go, thanks.
    • #8
      Members KZNSeagull's Avatar
      Join Date
      Nov 2007
      Location
      Wolsingham, County Durham
      Posts
      12,290


      0 Not allowed!
      Quote Originally Posted by darkwolf666 View Post
      This quote is hidden because you are ignoring this member. Show Quote
      Will give that a go, thanks.
      No Prob. If you dont want intermediate totals then you can use SUM adding each SUMIFS from above for each sheet:

      =SUM(SUMIFS(B2:B8,A2:A8,">="&E 2,A2 :A8,"<="&E3)+SUMIFS(B2:B8,A2:A 8,">="&E2,A2 :A8,"<="&E3)+SUMIFS(B2:B8,A2:A 8,">="&E2,A2 :A8,"<="&E3)) etc. Obviously you need to make sure the sheet names and columns are correct for each SUMIFS.
      Sanibona!!

Bookmarks

Posting Permissions

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