Results 1 to 12 of 12
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Easley, South Carolina, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Two Way Summing (Excel '97)

    See Attached Spreadsheet. Have 3 columns devoted to data entry. Date Built, Return Code, and Quantity. A fourth column shows all possible return codes. These are RT-01 through RT-27. Fifth column calculates the total quantity of returns by RT-Code. I have figured that one out. The sixth column should be similar to fifth column but has a Date filter so that it show returns for a Return Code and Date Range. Any assistance greatly appreciated.

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

    Re: Two Way Summing (Excel '97)

    Put the date (06/01/02 in your workbook) in a cell, for example in G2. Put this formula in cell F2:

    =SUM(($A$2:$A$50>$G$2)*($B$2:$B$50=E2)*$C$2:$C$50)

    and fill down as far as needed.

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Easley, South Carolina, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two Way Summing (Excel '97)

    Looks as if your answer is in the right format, except, substitute D2 for E2 and it works.

    How would formula look if Date is a range rather than just greater than a date.
    For example Dates between Jan 13, 2002 and Jun 15, 2003 inclusive?

    Thanks for prompt and very helpful reply.

    Regards,
    Thomas

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

    Re: Two Way Summing (Excel '97)

    Sorry about the mistake, I had been trying several things and forgot to correct for that.

    Split the "between ... and ..." condition into 2 separate conditions: (date greater than or equal to 01/13/02) and (date less than or equal to 01/15/03). Let's say that you put the limiting dates in G2 and G3. Put this formula in cell F2:

    =SUM(($A$2:$A$50>=$G$2)*($A$2:$A$50<=$G$3)*($B$2:$ B$50=D2)*$C$2:$C$50)

    and fill down as far as needed.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Two Way Summing (Excel '97)

    How about this in F2 and copy it down the column:
    =SUMPRODUCT(($A$2:$A$50>=$G$2)*($A$2:$A$50=<$G$3)* ($B$2:$B$50=D2)*$C$2:$C$50)
    G2 has minimum date (Jan 13, 2002), G3 the max date (Jun 15, 2003)

    Steve

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Two Way Summing (Excel '97)

    Hans,
    Doesn't this need a sumproduct to get what he wants? Or do I misunderstand his question?

    Steve

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

    Re: Two Way Summing (Excel '97)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> I forgot to mention that my formula is an array formula, so it must be confirmed with Ctrl+Shift+Enter. Your SUMPRODUCT formula returns the same value but as a normal formula, so yours is easier.

  8. #8
    Lounger
    Join Date
    Jan 2001
    Location
    Easley, South Carolina, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two Way Summing (Excel '97)

    I have tried the suggested formula, but it does not work as it always selects all dates for Codes, though I don't see why. See attached spreadsheet which is a sanitized version of the actual one I am attempting to use. Note the difference if one uses an Automated Filter to select dates >=Jun 1, 2002 as compared to using the suggested formula. The automated filter correctly selects 53 entries, the formula selects 72 entries.

    SUMPRODUCT(($C$3:$C$90>=$F$2)*($B$3:$B$90=E3)*$A$3 :$A$90) is the formula being used. The portion ($C$3:$C$90>=$F$2) is what is bombing.

    Any assistance would be appreciated.

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

    Re: Two Way Summing (Excel '97)

    I don't have enough time now to look deeply into this problem, but it is caused by the presence of the word "None" in the Weld Date column. If you delete all occurrences of "None", the results are the same.

  10. #10
    Lounger
    Join Date
    Jan 2001
    Location
    Easley, South Carolina, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two Way Summing (Excel '97)

    Hans

    Thanks for looking at problem. Your observation does allow me to investigate alternatives.

    Regards,
    Thomas

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Two Way Summing (Excel '97)

    Use this:
    =SUMPRODUCT(($C$3:$C$90>=$F$2)*($C$3:$C$90<>"None" )*($B$3:$B$90=E3)*$A$3:$A$90)

    When the "date" = "None" it is compared as if it were a zero in the previous formula, so of course it is a hit. Data filter gives the more correct answer as it is NOT greater than. This will fix it.

    Steve

  12. #12
    Lounger
    Join Date
    Jan 2001
    Location
    Easley, South Carolina, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two Way Summing (Excel '97)

    <img src=/S/clever.gif border=0 alt=clever width=15 height=15> Perfect. Does exactly what I needed. Many thanks to all that responded.

    Regards,
    Thomas

Posting Permissions

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