Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    4 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    572
    Thanks
    63
    Thanked 5 Times in 4 Posts

    Comparing two columns

    I have a worksheet with about 500 rows. Among other things, it contains payments received during various numbered time periods. There are 20 different time periods, each with its own number from 1-20. About 25 payments are received during each time period.

    Each row of the worksheet corresponds to a payment received during a given time period. I want to enter a period number into an unrelated cell, and have an adjacent cell show the number of payments over $1,000 received during that period.

    Column A is named Period, and contains the number of the time period for each payment.
    Column B is named Amount, and contains the amounts of the payments.
    Cell C1 is for user input, and will contain the number of one of the periods.
    Cell D1 will show the number of payments over $1,000 received during the period entered in C1.

    I need a formula to put in D1. I've played with this pretty much, and I keep coming up with errors. Who can help?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,054
    Thanks
    196
    Thanked 759 Times in 694 Posts
    Lou,

    See if this meets your needs?
    =SUMIFS(Amount,Period,$C$2,Amount,">1000")
    LouS Sumif.JPG
    Note: Amount & Period are named ranges b2:b9 & a1:a9 respectively. Using dynamic range names here will improve the spreadsheet's functionality.
    Lou Sander SumIfs.xlsx
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. #3
    4 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    572
    Thanks
    63
    Thanked 5 Times in 4 Posts
    I am using Excel 2003, and I get a #NAME? error. When I load your .xlsx file, Excel tells me that a function on your worksheet is not supported by my version of Excel, and will get a #NAME? error. I'm pretty sure that the SUMIFS function is the problem.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,054
    Thanks
    196
    Thanked 759 Times in 694 Posts
    Lou,

    Sorry, the sumifs function didn't appear until Excel 2007.
    We'll probably have to resort to a SumProduct function. I'm not real familiar with them but I know they can do the equivalent of the SumIfs. I'll work on it. If you're lucky Zeddy or Rory will pop in as they are both expert with this function. HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. #5
    4 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    572
    Thanks
    63
    Thanked 5 Times in 4 Posts
    I have used SUMPRODUCT elsewhere, I believe at the suggestion of Rory, and with the help of an example by him or somebody else. I tried to use it the same way here, but with no success. In the meantime I've done a workaround, but it isn't totally satisfactory.

    Every time I think I know Excel, I come up with an application that I don't know how to implement. Sheesh!
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,054
    Thanks
    196
    Thanked 759 Times in 694 Posts
    Lou,

    Don't feel bad. I've been working with it and its predecessors for going on 30+ years and I'm still learning.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,054
    Thanks
    196
    Thanked 759 Times in 694 Posts
    Lou,

    Ok I got it.
    =SUMPRODUCT((Period=C2)*((Amount>1000)*Amount))
    LouS Sumif.JPG

    Here's the file: Lou Sander SumIfs.xlsx HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  9. #8
    4 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    572
    Thanks
    63
    Thanked 5 Times in 4 Posts
    Quote Originally Posted by RetiredGeek View Post
    Lou,

    Don't feel bad. I've been working with it and its predecessors for going on 30+ years and I'm still learning.
    Me, too. And the really bad part at this point in life is that in the time it takes you to learn one new thing, you forget two or three old ones. Sheesh!
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  10. #9
    4 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    572
    Thanks
    63
    Thanked 5 Times in 4 Posts
    Quote Originally Posted by RetiredGeek View Post
    Lou,

    Ok I got it.
    =SUMPRODUCT((Period=C2)*((Amount>1000)*Amount))
    LouS Sumif.JPG

    Here's the file: Lou Sander SumIfs.xlsx HTH
    I still get a #NUM! error. Maybe something else is going on here. I will start again from scratch.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  11. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,054
    Thanks
    11
    Thanked 35 Times in 34 Posts
    =sumproduct((a2:a501=c1)*(b2:b501>=1000))

  12. #11
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,474
    Thanks
    22
    Thanked 168 Times in 164 Posts
    Hi Lou

    Try using the array formulas as in attached Excel2003 file.

    zeddy
    Attached Files Attached Files

  13. #12
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,054
    Thanks
    11
    Thanked 35 Times in 34 Posts
    =sumproduct((a2:a501=c1)*(b2:b501>=1000)) or

    =sumproduct((a2:a501=c1)*(b2:b501>1000))

    Tells HOW MANY are over 1000 (or over or equal to 1000) which is what I thought Lou was asking for.

  14. #13
    4 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    572
    Thanks
    63
    Thanked 5 Times in 4 Posts
    Well. After some fooling around, I discovered that my range names for Period and Amount were corrupt. I'd select one of the names by using the drop-down box that shows cell IDs and range names, and a certain group of cells would become selected. Then I'd do it again, and a different group of cells would become selected. Strange business. I fixed it by deleting both the names then redefining them.

    Just to be safe, I reformatted all the cells in the areas involved, putting everything in the appropriate numeric format. (You never know when one apparently numeric cell will be inadvertently formatted as Text.)

    Everything seems to work now.

    The formula that works is =SUMPRODUCT((Period=C2)*(Amount>1000))

    Whew!
    Last edited by Lou Sander; 2013-05-19 at 19:32. Reason: punctuation
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  15. #14
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,054
    Thanks
    11
    Thanked 35 Times in 34 Posts
    Lou, if you could have some of the period numbers entered as text, try this: =SUMPRODUCT(((1*Period)=C1)*(B2:B501>=1000))

    or: =SUMPRODUCT(((1*Period)=C1)*(B2:B501>1000))

  16. #15
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,054
    Thanks
    196
    Thanked 759 Times in 694 Posts
    Hey Y'all

    adjacent cell show the number of payments over $1,000
    DUH! Missed that completely! Thanks for setting me straight.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


Page 1 of 2 12 LastLast

Posting Permissions

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