| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!!! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!!! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!!! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!!! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help me to generate a report | New Users to Excel | |||
| Can I generate a report for my classroom? (I APPRECIATE YOUR HELP | Excel Worksheet Functions | |||
| Problem trying to generate a report with a formula | Excel Discussion (Misc queries) | |||
| Issue with a formula to help generate a report. | Excel Worksheet Functions | |||
| Generate Report | Excel Worksheet Functions | |||
Linear Mode
