1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 2003 Help with sum range of dates found

Discussion in 'Business Applications' started by Strybes, Mar 19, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. Strybes

    Strybes Thread Starter

    Joined:
    Sep 7, 2007
    Messages:
    67
    Hi

    I need help from one of you excel gurus out there.

    I will see if I can explain clearly what I am trying to achieve.
    I would like a macro that searches a worksheet for all debit transactions and credit transactions seperately for a given date and then sums them up. For example if i had two debit transactions on the 17th March, the macro finds the range for all transactions on the 17th and sums up those debits. Again the same for credits.
    Ok, here is where it gets trickier, I need it to do it for all days from 1/07 to 30/06. So it's searching to see if there are any transactions for any of those dates and then sums them up.

    I appreciate any assistance

    Thanks
    Strybes
     

    Attached Files:

  2. maxflia10

    maxflia10

    Joined:
    Feb 24, 2003
    Messages:
    331
    Does it have to be a macro? You can use SUMIF for this.
     
  3. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Like Max has already said you should be able to use a formula for this. Can you attach your sample workbook to your next post so that we can see the layout of your data?

    Regards,
    Rollin
     
  4. Strybes

    Strybes Thread Starter

    Joined:
    Sep 7, 2007
    Messages:
    67
    No, it doesn't need to be a macro, any solution would be great.

    I have attached a manualy example of what I am trying to achieve.

    As you will see the heading of the column refers to the tab with the data I am trying get. It would be desirable to have more debit and credit columns referring to other tabs that would also bring that data into the one sheet.

    Thanks for your assistance.
    Update example attached.

    Thanks
    Strybes
     

    Attached Files:

  5. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Why would you have the data on more than one tab? Can you not consolidate your data onto a single sheet, then use a pivot table?
     
  6. Strybes

    Strybes Thread Starter

    Joined:
    Sep 7, 2007
    Messages:
    67
    Hi firefytr

    The data is coming from different accounts that are downloaded indvidually. There are few groups involved and not all downloaded data is going to be consolidated into one sheet. There will be approx 4 consolidated sheets drawing in data from approx 8 accounts. Thus I need to present both the seperate accounts showing where the data originated from (so individuals can see that transactions match) and then the consolidated sheets so that the different groups see what their situation actually is.
    Please ask further questions if this is not clear enough.
    Thanks
    Strybes
     
  7. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    Hi Strybes,

    This is the fomula to calculate the debits (Column C)
    =SUMIF('Private 2882'!$A$3:$E$17,$A2,'Private 2882'!$C$3:$C$17)

    - change $C$3:$C$17 to $D$3:$D$17 for Credits.
    - change Private 2882 for Each Tab / Sheet

    And add a "+" for each additional Sheet (tab)
    =SUMIF('Private 2882'!$A$3:$E$17,$A2,'Private 2882'!$C$3:$C$17)+SUMIF('Private 2883'!$A$3:$E$17,$A2,'Private 2883'!$C$3:$C$17)

    =SUMIF(range, criteria, sum_range)

    You may want to use Name Ranges (Insert>Name>Define) to make it a little tidier & easier to read.
    ie. =SUMIF(Private2882,$A2,Private2882_Debit).

    Although I personally would consolidate the data into one sheet, and put in another columns with the names of each division (I.e. Private2882, Private2883), and use that to Filter or Advance Filter for anyone who needs their corresponding data afterwards. It'll look neater and you'll be able to do more with the data.

    - MRdNk
     
  8. Strybes

    Strybes Thread Starter

    Joined:
    Sep 7, 2007
    Messages:
    67
    Thanks MRdNk

    I will try it later today and let you know how it goes.
     
  9. Strybes

    Strybes Thread Starter

    Joined:
    Sep 7, 2007
    Messages:
    67
    Thanks MRdNk!

    That worked well.
     
  10. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    No problem, my pleasure.
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/695081

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice