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.
--
Brian

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


"Brian" wrote in message
...
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.
--
Brian

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

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" wrote in message
...
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.
--
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


"Gaurav" wrote:


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

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" wrote in message
...
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.
--
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.
--
Brian


"Gaurav" wrote:


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

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" wrote in message
...
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.
--
Brian

"Brian" wrote:
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"))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---