Extracting Data

[rmitchell87]

I am trying to write a formula that will search a worksheet and give me the
total number of units that were completed in a given month, i.e.,

Type Serial # Status RMA # Date Out

M50 22917 On Shelf 60016 18-Apr-07
M65 33836 On Shelf 60426 20-Apr-07
M50 11122 On Shelf 60435 24-Apr-07
AirNet301 12970 On Shelf 60592 25-Apr-07
M65 30631 On Shelf 60536 27-Apr-07
M65 34135 On Shelf 60537 27-Apr-07
MN W/P 36520 On Shelf 60593 30-Apr-07
MN W/P 34767 On Shelf 60625 30-Apr-07
AirNet301 12973 On Shelf 60639 1-May-07
MiniNet310 42419 On Shelf 60640 2-May-07

I have been trying the CountIf statement and trying to combine that with the
and/if statements and I am getting knowhere...

I just need to find out how many units, by type, were done in the month.

Thank you for any help that you can give me.

Rod

 

[Max]

One quick way ... try a pivot table (PT). First, remove the blank row2? so
that there's no gap between the top header row and the first row of data.
Then just select any cell within the source table, click Data > Pivot Table
.... Click Next > Next. In step 3, click Layout, then drag n drop "Type" into
ROW, "Date out" into COLUMN, "Date out" into DATA (it'll appear as Count of
Date out), click OK > Finish.

Hop over to the created PT sheet (it'll be placed just to the left of the
source sheet). Right click on "Date Out" > Group & Show Detail > Group .. In
the Grouping dialog, ensure By: "Months" is checked, click OK. That's it. The
PT will give you the required results (accomplishable in under a minute!)

 

[challa prabhu]

Hi,

It is recommended that you use Pivot table for Ananlysing large data list.

Do the following:

1. On the Data menu, click Pivot Table and Pivot Table chart.
The Pivot Table and Pivot Table chart wizard is displayed.
2. Under the "Where is the data you want to analyze?, click Microsoft Office
Excel list or database.
3. Under "What kind of Report you want to create?, click Pivot table.
4. Click Next.
5. In the Where is the data that you want to you box, type a reange or
select a range using the mouse pointer.
6. Click Next.
7. In the Step 3 of 3, click the Options button. The Pivot Table options
dialog box displays.
8. Under Format Options, click to select the Grand Total for Columns and
Grand Total for Rows check boxes
9. Click OK in the Pivot Table options dialog box.
10. Click Finish in the Pivot table and Pivot Table Chard Wizard.

Challa Prabhu