Hi all,
I would like to ask, whether here is somebody who could help me to solve my issue :-)
I have a list of data ==> Date, Server and Status of the server.
What I want is to have number of servers failing less than 2 times in a row, thanks 2 - 3 times and more than 3 times.
Not sure exactly how to do it hence I'm attaching the example
Thanks,
Try these array formulas**:
< 2 times in a row, entered in G2:
=SUM(IF(FREQUENCY(IF(C2:C50="Failed",ROW(C2:C50)),IF(C2:C50<>"Failed",ROW(C2:C50)))=1,1))
> 3 times in a row, entered in G4:
=SUM(IF(FREQUENCY(IF(C2:C50="Failed",ROW(C2:C50)),IF(C2:C50<>"Failed",ROW(C2:C50)))>3,1))
2 - 3 times in a row, entered in G3:
=SUM(IF(FREQUENCY(IF(C2:C50="Failed",ROW(C2:C50)),IF(C2:C50<>"Failed",ROW(C2:C50))),1))-G2-G4
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Can I use SUMIF, if I need to add 1 or 2 conditions?
No, SUMIF will not work for this.Originally Posted by dobracik
Actually is not, what I was looking for
I need to see in number of failing servers in a row ( like consecutive) .
The formulas I suggested DO WHAT YOU ASKED FOR.
If that's not what you wanted then explain what you do want and update the sample file to show what results you expect.
Well, I was not rude, hence would like to avoid capitals.
I've tested even your suggestion and when sorted based on column C, numbers are shows only for more that 3 times, where's number 1, which is not correct.
Don't understand what you mean when you say: "when sorted based on column C".
Column C contains only 2 different entries, Passed or Failed.
So, if you "sort" column C on Failed then this is what you end up with:
Data Range
What result do you expect for that data?
Hi,
I just wanted to say, that when I sorted based on column C, got number 1, which is weird for me. :-)
What I need is to see:
- Number of Servers that failed less than 2 consecutive days
- Number of Servers that failed 2 to 3 consecutive days
- Number of Servers that failed more than 3 times consecutive days
I've modified a bit the attached file, please find below.
Thank you a lot,
Andy
Update the file and show us what results you expect.
Ok, please find attached. I've tried to modify it, not sure, whether this would help.
The results of this should be summary of how many servers are failing in 2, 3 or mor than 3 consecutive days
Andy
Sorry, but I don't understand how you arrive at those results.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks