Display data based on criteria (e.g month reporting)

[nate777]

Hi Again,

I have been trying to create a monthly report based on data that I have in another worksheet(same workbook).
And, this is what I'm trying to do on the monthly Report sheet


System Impacted Date Reported(Col E)

GCMS 22jul06

Trackwise 20Aug06

IsoTrain 25Aug06



I want to only report incidents/systems occured in a certain calender month. I'm using advanced filters and a criteria like $E10=>'Report Parameters'!$E$4 and $E10<='Report Parameters'!$F$4.

Note: Report Parameters is another sheet where I have user enter start date manually entered in E4 and End date entered in cell F4.

Any suggestions or other approaches is appreciated.












[/list]

 

[venkat1926]

Take this example

IsoTrain 25Aug06
I don’t know how your dates are parked in separate column and wheehr they re entered as dates and not as text
Suppose the above date is in B2
Now
=month(B2) will give you 8
You can use this as criteria either for filtering or doing sum if or sumproduct.

In vba you can wrie

If month (some range)=8, then (that cell.entirerow. copy) and another destination cell. you can put it in a loop

These are only some approach to the subject.

 

[nate777]

Thanks Venkat!

The System Impacted and Date Reported are in seperate columns. Date Reported is NOT in text format. Can you explicitly show me the formula using either sumproduct or sumif? which i can then use as criteria for Adv Filtering.

Thanks so much

 

[venkat1926]

I hope I have understood. you have an example of three rows. Do you want to know how many impact occurred in August (in your data number of impacts occurred in august is 2)

I am giving your data in a spread sheet and the formula in B8. Is this what you want? Confirm please.

NOTE: THIS FORMULA IS TO BE INVOKED WITH CONTROL+SHIFT+ENTER.
venkat

Book1
	A	B	C	D
1
2	GCMS	22-Jul-06
3	Trackwise	20-Aug-06
4	IsoTrain	25-Aug-06
5
6
7
8		2
Sheet1

 

[nate777]

Hi Venkat! Thanks for your help

Your formula helps me partially. However, I would like to also display the system names( and other row data from my table not shown here).

Nate

 

[venkat1926]

NOT CLEAR

you can have any number of columns. only put the formula in an empty cell and modify the formula to denote the relevant columns.

venkat

 

[lenze]

Hi: Excuse me for butting in on this thread, but wouldn't a simple Pivot Table grouped by however you want do this in about 10 seconds? Unless I missed something, there's no need to get creative when a built-in tool is available

lenze