Two Way Summing (Excel ’97)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Steve

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Hans

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

Regards,
Thomas

Reply | Quote

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

Reply | Quote

Perfect. Does exactly what I needed. Many thanks to all that responded.

Regards,
Thomas

Reply | Quote