Six Months

[adam]

Hi anyone,

I’m trying to figure out a formula or code that would total the sum of the product received within the prior six months to the date written in the sheet “NewOrder”.

Suppose if today’s date, 7/11/2010 is written in cell F177 of “NewOrder” sheet I want the total amount of chai that is received from the previous six months to get added to the cell “Q18” in parallel to the row where chai contains. And the same for other products.

I hope I’ve made my question clear.

Thanks in advance

[HansV]

Does this do what you want?

=SUMPRODUCT((OrderData!$A$12:$A$41>DATE(YEAR($F$177),MONTH($F$177)-6,DAY($F$177)))*(OrderData!$E$12:$E$41=E18)*OrderData!$G$12:$G$41)

[adam]

Yes Hans. Thanks for the help.

[adam]

When the date from the cell F177 is cleared the column containing the formula show #NUM!.

How could this be avoided.

[HansV]

In general, if a formula

=MyFormula

returns an error, you can suppress this by using

=IFERROR(MyFormula,"")

This works only in Excel 2007 or higher; if you want a formula that works in all versions, use

=IF(ISERROR(MyFormula),"",MyFormula)

[adam]

=IFERROR(SUMPRODUCT((OrderData!$A$12:$A$41>DATE(YEAR($F$177),MONTH($F$177)-6,DAY($F$177)))*(OrderData!$E$12:$E$41=E18)*OrderData!$G$12:$G$41)),"")

The formula is showing a zero value. What have I done wrong here?

[HansV]

You haven't read my previous reply carefully enough. There is a superfluous parenthesis ) in your formula.

[adam]

The following modification does what had been asked. Thanks for the help Hans.

=IFERROR(SUMPRODUCT((OrderData!$A$12:$A$41>DATE(YEAR($F$12),MONTH($F$12)-6,DAY($F$12)))*(OrderData!$E$12:$E$41=E18)*OrderData!$G$12:$G$41),"")

In the attached workbook how could the formula be adjusted so that when the user writes the date in the cell F12 and selects a month range period from the cell F14 either 1 month or more, the column Q would show the amount received 1 month back from the date written in cell F12.

And same or the rest.

I hope I've made my question clear.

[HansV]

=IFERROR(SUMPRODUCT((OrderData!$A$12:$A$41>DATE(YEAR($F$12),MONTH($F$12)-LEFT(F14,FIND(" ",F14)-1),DAY($F$12)))*(OrderData!$E$12:$E$41=E18)*OrderData!$G$12:$G$41),"")

[adam]

Thanks for the help Hans.

Having the date written in the cell F12 as 2/12/2010 and the order period as 12 months, the column Q of the sheet NewOrder shows 55 instead of showing 65 when I add a row to the sheet OrderData with the date 2/12/2010 and the product as chai with a quantity of 10.

What may be the reason for this?

[adam]

sorry quantity with the date 2/12/2010 is 15.

[HansV]

The formula looks at rows 12:41 of the Order Data sheet. If you enter data in row 42, it won't be taken into account - so you have to adjust the ranges in the formula.