Calculation based on date and name


Board Regular
Oct 26, 2005
Office Version
  1. 2016
  1. Windows

I am trying to work out the Meant Time Between Failures for Servers during a particulare three month period.

I already work out the MTBF for a particular server for all the data available as follows

IF('Availability Rankings'!C3=0," ",(hrsofservice/COUNTIF(INDIRECT("'Network Stats Unplanned'!"&rangetouse),A22))/24)

Where the rangetouse can be adjusted to pick out parts of the spreadsheet and A22 is the location of the Server name.

In the main spreadsheet there is a column 'N' which has the Month number in (1 to 12) and I want to do the calculation above based on the server name and 3 months of my choice (eg, 7, 8, 9)

Any ideas.

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
I think you've asked a very similar question before.

The answer is still the same, in my opinion.

You need a SUMPRODUCT to replace the COUNTIF function, because SUMPRODUCT can handle multiple criteria.

Your problem then is that you are using a variable range for COUNTIF so you need a way to apply this within SUMPRODUCT so that your ranges for different criteria are the same size
Upvote 0
Unfortunately I don't know how to integrate the Countif into the SumProduct calculation which is why I entered the question.
Upvote 0
What can rangetouse be?

Would it always be a range within one predetermined column? If so which?

Assuming it's column D, for instance

=IF('Availability Rankings'!C3=0," ",(hrsofservice/SUMPRODUCT(--(INDIRECT("'Network Stats Unplanned'!"&rangetouse)=A22),--ISNUMBER(MATCH(OFFSET(INDIRECT("'Main spreadsheet'!"&rangetouse),,10),{7,8,9},0))))/24)

the 10 in the formula denotes the number of columns from D to N so if your rangetouse is in column E then this needs to be 9 instead.

I'm not sure which sheet is your "main spreadsheet", substitute the correct sheet name
Upvote 0
rangetouse is just the cell range for the server names (eg A24:A43) I am basically adding together the downtime in column G for all occurrences of a particular server name in column A. I need now to be able to do this with the additional criteria of the three months of my choice from Column N (This column shows the months as 1 to 12)

I am not sure from your previous formula was trying to achieve and what you understood as the rangetouse but it just comes back with a#div/0

The main spreadsheet is called Network Stats Unplanned
Upvote 0
I don't see any reference to column G in the formula you quoted initially, is that the full formula?

How are you defining rangetouse, is that a named range or a single cell with a range shown within it?

I think you probably need a SUMPRODUCT formula which, in essence, should be fairly simple, it would just add together the values in G for the rows where your criteria are satisfied.

However SUMPRODUCT requires all ranges to be the same size so you can't have a variable range for one criteria without that affecting the other criteria.

Would it be simpler, first of all, to find a formula which would give you a result for a fixed range and then work towards a solution with more flexibility?
Upvote 0

I think we are going in circles now but I do appreciate you spending the time. I have to refer you to my original formula for my MTBF calculation. This gives me the calculation for the year. Basically it counts the number of times a server name appears in column A in my main spreadsheet(therefore the number of failures) and I divide this figure into the service hours to get the Mean Time Between Failure.

IF('Availability Rankings'!C3=0," ",(hrsofservice/COUNTIF(INDIRECT("'Network Stats Unplanned'!"&rangetouse),A3))/24)

The divide by 24 just puts the result into days.

What I want to do is to run the above calculation for not for the full year(spreadsheet to date) but for 3 months of my choice by some how using Column N in my spreadsheet which gives the month number from 1 to 12.

No other columns of my spreadsheet are required for the MTBF calculation.

Thanks again.
Upvote 0
I may be barking up the wrong tree (or possibly it's not even a tree at all :) ) but I think the previous formula still stands with a little adjustment

=IF('Availability Rankings'!C3=0," ",(hrsofservice/SUMPRODUCT(--(INDIRECT("'Network Stats Unplanned'!"&rangetouse)=A22),--ISNUMBER(MATCH(OFFSET(INDIRECT("'Network Stats Unplanned'!"&rangetouse),,13),{7,8,9},0))))/24)

where rangetouse is a range within column A something like your earlier example A24:A43 and the corresponding range in column N contains numbers denoting the month.
Upvote 0

That worked fine and I can now calculate my MTBF automatically for the 3 month periods, thank you for all your trouble with that. The problem I have now is to calculate the Mean Time to Repair for a server over the 3 month period.

This entails adding all the downtime (in column G) for a particular server name (in Column A) for the specified months (Column N), then dividing this figure by the number of failures to get the MTTR.

To get the MTTR for a server for the year today (complete spreadsheet) I use the following formula,

IF('Availability Rankings'!C3=0," ",(SUMIF(INDIRECT("'Network Stats Unplanned'!"&rangetouse),A3,'Network Stats Unplanned'!$G$7:$G$500)/COUNTIF(INDIRECT("'Network Stats Unplanned'!"&rangetouse),A3)))

A3 = Server Name
rangetouse = range of cells in Column A of spreadsheet
G7:G500 = range of cells to select downtime from

You got your magic wand handy?
Upvote 0
Based on the formula I suggested above you could try this array formula

=IF('Availability Rankings'!C3=0,"",AVERAGE(IF((INDIRECT("'Network Stats Unplanned'!"&rangetouse)=A3)*(ISNUMBER(MATCH(OFFSET(INDIRECT("'Network Stats Unplanned'!"&rangetouse),,13),{7,8,9},0))),OFFSET(INDIRECT("'Network Stats Unplanned'!"&rangetouse),,6))))

must be confirmed with CTRL+SHIFT+ENTER so that curly braces appear around formula in formula bar

....but this one may be preferable as it replaces OFFSET with SUBSTITUTE....also must be confirmed with CTRL+SHIFT+ENTER

=IF('Availability Rankings'!C3=0,"",AVERAGE(IF((INDIRECT("'Network Stats Unplanned'!"&rangetouse)=A3)*(ISNUMBER(MATCH(INDIRECT("'Network Stats Unplanned'!"&SUBSTITUTE(rangetouse,"A","N")),{7,8,9},0))),INDIRECT("'Network Stats Unplanned'!"&SUBSTITUTE(rangetouse,"A","G")))))
Upvote 0

Forum statistics

Latest member

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
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 "".
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