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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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

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

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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
 

nate777

New Member
Joined
Aug 2, 2006
Messages
27

ADVERTISEMENT

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

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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

Legend
Joined
Feb 18, 2002
Messages
13,690
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
 

Forum statistics

Threads
1,137,198
Messages
5,680,114
Members
419,881
Latest member
Rubber Soul

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
Top