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 2007 - Conditional SUM formula problem

Discussion in 'Business Applications' started by greigorius, Jan 12, 2011.

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

    greigorius Thread Starter

    Joined:
    Jul 12, 2009
    Messages:
    14
    Hi,

    I am having trouble getting results from a formula that is trying to return the sum of numerous values in a field once 2 conditions are met. I would say I have between novice and intermediate experience with excel formulas so I hope I can explain it clearly.

    Worksheet 1: results

    Worksheet 2: data
    Column 1 (month): April, April, April, May, June, June, July, July, etc...
    Column 2 (code): 4000, 6500, 4902, 4902, 3002, 4000, etc...
    Column 3 (amount): £0, £0, £3598, £725, £0, £2212, £0, etc...

    I want to get the sum of the amounts in Column 3 for the code in Column 2 in a specific month. For example, how much was spent in April for code 4902?

    I am working with this formula
    =SUM(IF(Worksheet2!$A$6:$A$245="April",IF(Worksheet2!$B$6:$B$246=4902,Worksheet2!$C$6:$C$246,0),0))

    However, this is returning errors.

    Can anyone help solve this one, perhaps with a simpler formula?

    Thanks
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    I don't think there is a conditional formula that does this in one step.
    I don't know if a macro will be an option for you since these are not always allowed on some networks.
    Maybe you'll have to breakdown the selections into two or three steps and then add the found results.
    I would suggest an extra table with let's say the names of the month accrowss and the list of codes as rows (unique values)
    Then I think a a macro would be the best solution to do all the calculating.
    drop a line and let us know if this would be an option.
     
  3. greigorius

    greigorius Thread Starter

    Joined:
    Jul 12, 2009
    Messages:
    14
    Hi,
    Thanks for the speedy response. I'm not on a network so I guess a macro can be used, only I don't know a lot about them. If it's as straight forward as copying code into a new module in Visual Basic then I will be happy to give it a go. However, I am also interested in breaking the information down further into more tables if you have suggestions for this method too.
    Thanks
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    I'll need your input to know what you need.

    I'll see if I can put together a simple macro to show the costs per month per code
     
  5. greigorius

    greigorius Thread Starter

    Joined:
    Jul 12, 2009
    Messages:
    14
    I have attached a sample workbook to help give you an idea of the problem. You'll see that I need the calculation for both credit and debit amounts for each month.

    Look forward to seeing what you come up with.
     

    Attached Files:

  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    I see what you want, I'll edit the code I wrote and see how fast I can put it to work, in the attached file see my first step with the data I took from the post
     

    Attached Files:

  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    BTW, you don't have all the nominal codes in the Results sheet, is this intentional?
     
  8. greigorius

    greigorius Thread Starter

    Joined:
    Jul 12, 2009
    Messages:
    14
    I thought I could just give you a sample and then I could transpose the formula/macro across the rest of the sheet. Anyway, I have attached a version with all nominal codes.
    Thanks
     

    Attached Files:

  9. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
  10. greigorius

    greigorius Thread Starter

    Joined:
    Jul 12, 2009
    Messages:
    14
    Hi etaf,
    Thanks for the response. This was the first thing I tried but it would only return the number of cells that contained a value and not the sum of those cells...? It's very frustrating because I'm sure there is a simple fix to this.
     
  11. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    i dont want to stop Keebellah work with macros here
    You may want to look at the pivot table option - the way you have the datasheet and also the layout you want - lends itself to a Pivot Table

    I have attached the table here
     

    Attached Files:

  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    Hij etaf, I think that's the solution I needed for our poster.
    It's quick and versatile, thanks (y)

    VBA is easy but not always the most indicated since it creates a dependancy.

    I'll do some coding just for the 'brain gymnastics' anyway. :)
     
  13. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    Hi Keebellah , it looked like it might be and with some changes to format you can make the sheet look quite presentable also easily updated - I'm very rusty on macros - but like to follow your posts to keep my hand in a little

    Lets see what the OP has to say, as there maybe more twists which require the macro
     
  14. greigorius

    greigorius Thread Starter

    Joined:
    Jul 12, 2009
    Messages:
    14
    Hi guys,

    No hidden twists, this is precisely what I need. I have not used pivot tables so much but know people who swear by them - I can now see why.

    Thanks very much.
     
  15. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
  16. 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/974188

  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