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

nate777

New Member
Joined
Aug 2, 2006
Messages
27
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]
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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.
 
Upvote 0
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
 
Upvote 0
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
ABCD
1
2GCMS22-Jul-06
3Trackwise20-Aug-06
4IsoTrain25-Aug-06
5
6
7
82
Sheet1
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,211,877
Messages
6,104,521
Members
447,914
Latest member
karl10220

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top