Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jun 2003
    Location
    Singapore, Singapore
    Posts
    39
    Thanks
    0
    Thanked 1 Time in 1 Post

    Need to automate summing numbers in an range of da (Excel 2003 SP)

    Hi Guys,
    I have a problem that I'm sure is easy to solve, it just goes beyond my limited programming skills!
    In the attached spreadsheet, potential sales are split into months in column A. In column D the estimated chance in percent is recorded. The values in this column can be anything from zero to 100 in steps of 10. To reduce the number of steps, in column I, the potential chance of a sale is reduced to 0-49 ; 50-69 ; 70-99 and 100 percent. (I use the inelegant LOOKUP function, if there is a better way, it would be nice to know it - the number of rows will grow and diminish by the way depending on the potential sales reported).

    The challenge is to create a formula per month to add the potential sales values in column G (the MRV) in a small table at the top of the sheet in cells B2 to E5 in such a way that each of the the potential sales in column G that have been identified as falling into one of the four percentage chance ranges (0-49 ; 50-69 ; 70-99 and 100 percent) are added together to form a total that represents the sum of all the potential sales for the particular percentage chance range. At present this has been done by hand.

    As noted earlier, the data is not a constant number of rows as the monthly potential sales will increase as the year increases, but not at a constant rate per month.

    It would be really neat if someone could help me with a formula to do what I've described and in addition, provide a neater way of doing the LOOKUP function that I've used.

    Look forward to hearing from you guys!
    Cheers, Davy
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need to automate summing numbers in an range of da (Excel 2003 SP)

    See the attached version.
    I created a small lookup table so that you can use the VLOOKUP function to find the percentage range.
    I changed A2:A5 to April, May etc. to be able to compare the values in A8:A38 to these month names.
    I removed trailing spaces from A8 etc. because they prevented the formulas from working properly.
    I created a SUMPRODUCT formula in B2:

    =SUMPRODUCT(($A$8:$A$1000=$A2)*($I$8:$I$1000=B$1)* $G$8:$G$1000)

    and filled it down, then right, and formatted the cells with 0 decimal places and thousands separator.
    Attached Files Attached Files

  3. #3
    Lounger
    Join Date
    Jun 2003
    Location
    Singapore, Singapore
    Posts
    39
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Need to automate summing numbers in an range of da (Excel 2003 SP)

    Hi Hans,

    This is an excellent solution. Just what I was looking for. Thank you very much for your help.

    Cheers,

    Davy

Posting Permissions

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