Formula needed to generate report!
 

Dear all,
Help is needed!!! Currently I have a excel spreadsheet showing the following:

Part A 1 15-Jan-08
Part B 1 15-Jan-08
Part B 1 7-Jan-08
Part A 1 7-Jan-08
Part A 0 7-Jan-08
Part A 0 7-Jan-08
Part B 1 7-Jan-08
Part A 1 7-Feb-08
Part A 1 7-Feb-08

What formula can I use in order to generate a report to show that the count
for Part A is "2" under the month Jan 08 shown below:

Jan 08' Feb
Part A 2 2
Part B 3 0

Please help...... Thanks!!!

Formula needed to generate report!
 

Hi Astro

=SUMPRODUCT(($A$1:$A$100="Part A")*(MONTH($B$1:$B$100)=1))

Better to put the Month number ( 1 for Jan, 2 for Feb etc. ) in a cell, also
the part number in a cell
then
=SUMPRODUCT(($A$1:$A$100=$D$1)*(MONTH($B$1:$B$100) =$E$1))

I make the count 4 for your sample data - not 2


--
Regards
Roger Govier

"Astro" wrote in message
...
Dear all,
Help is needed!!! Currently I have a excel spreadsheet showing the
following:

Part A 1 15-Jan-08
Part B 1 15-Jan-08
Part B 1 7-Jan-08
Part A 1 7-Jan-08
Part A 0 7-Jan-08
Part A 0 7-Jan-08
Part B 1 7-Jan-08
Part A 1 7-Feb-08
Part A 1 7-Feb-08

What formula can I use in order to generate a report to show that the
count
for Part A is "2" under the month Jan 08 shown below:

Jan 08' Feb
Part A 2 2
Part B 3 0

Please help...... Thanks!!!

Formula needed to generate report!
 

Looks like you have a third condition - column B is greater than 0.
Try this:

=SUMPRODUCT(($A$1:$A$100="Part A")*($B$1:$B$1000)*(MONTH($C$1:$C$100)
=1))

If you have other years in there, you might like to have another term
like this:

*(YEAR($C$1:$C$100)=2008)

Hope this helps.

Pete

On Feb 11, 6:01*am, Astro wrote:
Dear all,
Help is needed!!! Currently I have a excel spreadsheet showing the following:

Part A *1 * * * 15-Jan-08
Part B *1 * * * 15-Jan-08
Part B *1 * * * 7-Jan-08
Part A *1 * * * 7-Jan-08
Part A *0 * * * 7-Jan-08
Part A *0 * * * 7-Jan-08
Part B *1 * * * 7-Jan-08
Part A *1 * * * 7-Feb-08
Part A *1 * * * 7-Feb-08

What formula can I use in order to generate a report to show that the count
for Part A is "2" under the month Jan 08 shown below:

* * * * Jan 08' Feb
Part A *2 * * * 2
Part B *3 * * * 0

Please help...... Thanks!!!

Formula needed to generate report!
 

Well spotted, Pete.
I hadn't noticed there was a column B with numbers.

Another way of dealing with Year and Month would be to enter the date in E1
as 01 Jan 09 then
=SUMPRODUCT(($A$1:$A$100=$D$1)*
(TEXT($C$1:$C$100,"yymm")=TEXT($E$1,"yymm")*$B$1:$ B$100)


--
Regards
Roger Govier

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi Astro

=SUMPRODUCT(($A$1:$A$100="Part A")*(MONTH($B$1:$B$100)=1))

Better to put the Month number ( 1 for Jan, 2 for Feb etc. ) in a cell,
also the part number in a cell
then
=SUMPRODUCT(($A$1:$A$100=$D$1)*(MONTH($B$1:$B$100) =$E$1))

I make the count 4 for your sample data - not 2


--
Regards
Roger Govier

"Astro" wrote in message
...
Dear all,
Help is needed!!! Currently I have a excel spreadsheet showing the
following:

Part A 1 15-Jan-08
Part B 1 15-Jan-08
Part B 1 7-Jan-08
Part A 1 7-Jan-08
Part A 0 7-Jan-08
Part A 0 7-Jan-08
Part B 1 7-Jan-08
Part A 1 7-Feb-08
Part A 1 7-Feb-08

What formula can I use in order to generate a report to show that the
count
for Part A is "2" under the month Jan 08 shown below:

Jan 08' Feb
Part A 2 2
Part B 3 0

Please help...... Thanks!!!

Formula needed to generate report!
 

On Tue, 10 Feb 2009 22:01:01 -0800, Astro
wrote:

Dear all,
Help is needed!!! Currently I have a excel spreadsheet showing the following:

Part A 1 15-Jan-08
Part B 1 15-Jan-08
Part B 1 7-Jan-08
Part A 1 7-Jan-08
Part A 0 7-Jan-08
Part A 0 7-Jan-08
Part B 1 7-Jan-08
Part A 1 7-Feb-08
Part A 1 7-Feb-08

What formula can I use in order to generate a report to show that the count
for Part A is "2" under the month Jan 08 shown below:

Jan 08' Feb
Part A 2 2
Part B 3 0

Please help...... Thanks!!!

For generating a report, consider a pivot table

Label your columns.
Insert/Pivot Table or Data/Pivot Table
Drag dates to Rows area
Drag Part Names to Columns area
Drag the count to the data area.

Right-click on the Rows area in the pivot table and select Group. Then select
Months and Years.

Format to taste
--ron