Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    See attached sample spreadsheet

    I've struggled with this problem for a while. I wish summarize the "this year" & "last year" fees into groups. e.g. 0 to 1000, 1001 to 2000, 2001 to 3000 etc.
    For each group I wish count how many invoices were in each group. I've achieved that in the sample, but its not flexible with amounts embedded in formulas (something I try to avoid).

    I was hoping the Lounge could help improve the flexibility to allow changes to the group thresholds without changing the formulas. I thought SUMPRODUCT would meet my needs but I can't seem to get it to work as I don't fully understand how it might work with this data.

    Thanks for you help.

    Matthew
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    See Attached. I have modified your existing sum formulas and added two columns to show an alternative way to calc the frequencies. I did not update your current frequency formulas, but I expect you can do that yourself using the example sumif formulas I gave.
    Attached Files Attached Files
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Star Lounger
    Join Date
    May 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by pieterse View Post
    See Attached. I have modified your existing sum formulas and added two columns to show an alternative way to calc the frequencies. I did not update your current frequency formulas, but I expect you can do that yourself using the example sumif formulas I gave.

    Thanks Jan,

    Your changes achieve what I want & I've a new function (FREQUENCY) to study up on. Until now I've never seen it applied, so didn't understand how it worked. I think I could use that function in several other worksheets I have.

    Regards,

    Matthew

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Just remember that it is an array function -entered with control+shift+enter- in all cells that contain the function at the same time.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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