Regarding Arrays

[Raj]

Hi
Here is the excel data how my data is distributed.

Incident Description Status Date logged
313 XXXXX Review December 9, 2008 2:16 PM
GMT

There are thousands incidents with different status looged in these year.
Now i would like to See the no.of incidents which are in review status
logged on December 9. Can you please suggest me an array extract this data

 

[Gary''s Student]

Use AutoFilter:

1. click on the headercell containing "Status" and pull-down:

Data > Filter > Autofilter

This will result in pull-downs for each header cell

2. click on the Status pull-down and select "review"
3. click on the Dat oull-down and select the desired date.

 

[Pete_UK]

You can get a count of those records with this formula:

=SUMPRODUCT((C1:C1000="Review")*(TEXT(D11000,"dd mmm yy")="09 Dec
08"))

Change the ranges to suit.

You can also "see" the records by applying filters to the status and
date columns. Then you could highlight the visible records and <copy>
them to another sheet to produce an extract.

Hope this helps.

Pete

 

[Max]

.. the no.of incidents which are in review status logged on December 9

Try something like this, normal ENTER:
=SUMPRODUCT((C2:C100="Review")*(TEXT(D2100,"ddmmmyyyy")="09Dec2008"))

The above presumes data in col D (date logged) is cleaned up as real dates

With col D "as-is", you could try
=SUMPRODUCT((C2:C100="Review")*(TEXT(SUBSTITUTE(D2100,"GMT",""),"ddmmmyyyy")="09Dec2008"))
which removes the "GMT"


or perhaps even
=SUMPRODUCT((C2:C100="Review")*(TEXT(SUBSTITUTE(D2100,CHAR(10)&"GMT",""),"ddmmmyyyy")="09Dec2008"))
which removes both the preceding hard return & "GMT"

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik

 

[Raj]

Hi Max
I am unable to do this if i use the Formula u have give its giving the Value
N/A
Here is the exact sheet format
A B C D E
F G H
31490 XXXX XXXX XXX XXXX Review XXXX December 9, 2008 05:00 PM GMT+01:00

I have placed all the data in the Sheet named Requests. Now i want to pull
the data to Summary Sheet with the Count total incidents which are in review
Status (Colmun F) which are logged on December 9(Column H)

Please do helpme to resolve

 

[Max]

In Requests,
Use a helper col I to clean-up col H

In I2, copied down:
=IF(H2="","",IF(ISERROR(SEARCH("GMT",H2)),H2,SUBSTITUTE(H2,MID(H2,SEARCH("GMT",H2),99),"")+0))

Then in your Summary sheet, use
=SUMPRODUCT((Requests!F2:F100="Review")*(TEXT(Requests!I2:I100,"ddmmmyyyy")="09Dec2008"))
to return the desired count

P/s: Remember to press the YES button below ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik