Match product/date/amount for monthly totals


Posted by Dwight on September 28, 2001 9:51 AM

M2:M200 is a range for entering one of any of 20 product types; one example is "ACH". N2:N200 is for entering the corresponding date for a sale of the product sometime during the year 2002. O2:O200 is for entering the dollar amount of each sale. For instance, M2 might read "ACH", N2 "1/5/02" and O2 "$1200". This would represent one sale.

I need to create a spreadsheet which will show the sales of each product type by month. For instance, cell A2 would read "January", A3 "February", and so on through A13 ("December"). Cell B1 would read "ACH", cell B2 would contain a formula returning the total dollar amount for sales of ACH which took place during January 2002, B3 would represent ACH sales for February 2002, and so on.

If someone is brilliant enough to tell me what formulas would go in B2 and B3, I can probably complete from there.

Many thanks in advance.
Dwight



Posted by Aladin Akyurek on September 28, 2001 10:17 AM


Hi Dwight ---

A new project I presume.

In B2 enter: =SUMPRODUCT(($M$2:$M$200=B$1)*(TEXT($N$2:$N$200,"mmmm yyyy")=A2)*($0$2:$0$200)

I assumed that A2 is text-formatted, that is, it contains "January 2001" as text not as date.

Aladin

This can be copied across then down.