# Thread: Two Way Summing (Excel '97)

1. ## 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. ## 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. ## 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?

Regards,
Thomas

4. ## 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. ## Re: Two Way Summing (Excel '97)

=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. ## 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. ## 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. ## 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. ## 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. ## Re: Two Way Summing (Excel '97)

Hans

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

Regards,
Thomas

11. ## 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. ## 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
•