Product by Month formula

[Brian]

I need a formula which will count the number of sales of each product by
month where there is only one cell entry for each sale. Column A is the date
the sale is made; Column B is the type of product (eg. car, wagon, RV). I
want a formula that can tell me how many sales of each product occured in
January, February, etc.
Again, each sale is entered on a different row, so I think the formula would
somehow just count the number of cells in the date range that also related to
each product. Can someone help?
thanks.

 

[Bernie Deitrick]

Brian,

Select your data table, then choose Data / Pivot Table, and click Finish.

Then drag the "date" button to the Row Field, and the "type" button to the column field, and "type"
button again to the Data Field.

Right-click on the date and choose "Group and show detail" then "Group", and select "Months" in the
"By" selection field, and you're done.

HTH,
Bernie
MS Excel MVP

 

[Gaurav]

For Counting Wagons in January
=SUMPRODUCT((MONTH($A$2:$A$20)=1)*($B$2:$B$20="Wagon"))

For Feb, change 1 to 2. 3 for march and so on. Instead of typing wagon, try
using a cell reference. Suppose you have Wagon in D2 then use the following.
=SUMPRODUCT((MONTH($A$2:$A$20)=1)*($B$2:$B$20=D2))

 

[Brian]

Thank you so much!!!! I spend about 15 hours trying to develop a formula and
even bought a $30 Excel book, to no avail. But then Gaurav came along and
saved the day. I can't thank you enough.

 

[Brian]

Perfect. But one more thing. What if I want by month and year? That is, I
need to separate January 2007 from January 2008. thanks.

 

[Max]

What if I want by month and year? That is, I
need to separate January 2007 from January 2008.

You could use TEXT, like this:
=SUMPRODUCT((TEXT($A$2:$A$20,"mmmyy")="Jan07")*($B$2:$B$20="Wagon"))
=SUMPRODUCT((TEXT($A$2:$A$20,"mmmyy")="Jan08")*($B$2:$B$20="Wagon"))

---