Retrieving data from last 90 days

kreases

Board Regular
Joined
Oct 26, 2005
Messages
114
Office Version
  1. 2016
Platform
  1. Windows
I have a spreadsheet which shows lines and lines of faults for various servers. What I need to be able to do is to show in a seperate worksheet how many failures there has been of a particular server in the last 90 days

Any ideas?
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Without more information it's difficult to suggest a way forward but....

If you have a dates in column A and server identities in column B something like

=SUMPRODUCT(--(A1:A100>Today()-90),--(B1:B100="xyz123"))

would count the number of rows within the specified range where the date is in the last 90 days and server is "xyz123"
 
Upvote 0
sumproduct would probably do the trick, but we need more info on the structure of your data table. which data is in what column?

=sumproduct(--(ServerNameList=ServerName),--(FaultCount))

whoops, excuse me barry. We posted at same time. :)
 
Upvote 0
Well I tried the formula based on what you said but I get back #REF. I have used the following formula

=SUMPRODUCT(--('Network Stats'!D7:D717>TODAY()-90),--(INDIRECT("'Network Stats'!"&rangetouse=svr)))

where rangetouse is a named cell where I insert range numbers and svr is a named cell where the server name is inserted.

The fault data is in a different worksheet to the one where the calculation will take place and is made up of

Server Names Column C

Failure date Column D

So basically what I am trying to do is to say how many failures there have been for a particular server over the last 90 days.
 
Upvote 0
I have now got it working using the formula I mentioned above, I had to rekey the information in the 'rangetouse' cell. Not sure why this was a problem but it is working fine now, thanks for your time.
 
Upvote 0

Forum statistics

Threads
1,214,723
Messages
6,121,144
Members
449,013
Latest member
LuBurt

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