Results 1 to 12 of 12
Thread: Two Way Summing (Excel '97)

20030710, 19:54 #1
 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 RT01 through RT27. Fifth column calculates the total quantity of returns by RTCode. 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.

20030710, 21:17 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20030710, 22:08 #3
 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

20030710, 22:26 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20030710, 22:35 #5
 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

20030710, 22:38 #6
 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

20030710, 22:51 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20030711, 10:52 #8
 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.

20030711, 11:02 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20030711, 11:53 #10
 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

20030711, 17:13 #11
 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

20030711, 18:10 #12
 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