In excel pivotal table count unique values

Question

An Excel table contains information when a component of a service is down. So service [column A] (MyService) has a number of components [column B] (component-1, component-2, component-3). Each row means that a component was down for say 5 minutes [time-of-day]. Other columns are used for filters, etc.

A service is considered down when any of the selected components (component-1, component-2) is down. So, the total unavailability time is considered 5 minutes, even when both component-1 and component-2 were down at the same moment. So the unavailability is NOT multiplied when more components are down for the service.

We would like to know how much time a service (as a series of selected components) was unavailable per day. We use a pivotal table. In the rows are the components. In the columns are the time-of-days. The rows are summarized to a day level.

The total unavailability time for the day should be 2 (and not 3) for June 12. So, that would be:

So, how to "summarize" the unavailability of the service per day without doubling unavailability of different components at the same moment?

Try-2: I tried working in the Pivotal table with 'max'. Then the "summary" of the column for a time-of-day is ok, but then the summary is the max of all columns for a day, i.e. 1. Alas.

Try-3: Essentially the value per day should be the number of different time-of-days! Or the number of different time-of-day columns per day. How can I show that number for a day?

Answer 1

If you cannot insert a Helper Column into your existing table, you can follow the steps below.

If you are using Excel 2013 and above, when you first insert a pivot table, there should be an option to add data to data model which would be the last option in the dialog box as shown in the screenshot below.

After that, just continue with pivot table insertion procedure as usual and build you pivot table as shown in your question.
Now you can just select Distinct Count as Value Field Settings under the Values pane.

I think you are using a version of Excel higher than or equal to 2013 (guessing from your screenshot).
If your version is lower than that, e.g., 2010, you'll need to install PowerPivot Addin (downloadable from MS website).
After installation, you just need to activate the addin via Options->Add-ins->Manage (COM Add-ins) -> Go -> tick PowerPivot and OK.
Then import the data into PowerPivot and carry out the usual steps.
It is quite straight forward to do it with PowerPivot (PowerPivot will insert the resultant Pivot Table back into the .xlsx with modified data intact) but if you require assistance, let me know and I will outline the steps with screenshots.

Answer 2

So, just to get a 1 if any value in the range is 1:

=if(countif(range,"="&1)>=1,1,0)

Or, to test if equal or greater than 1:

=if(countif(range,">="&1)>=1,1,0)

Answer 3

If you are allowed to add a helper column, you can do it like this:

In this case the helper column is using this formula in cell J6:

=IF(SUM(IF($A$1:A5 & $C$1:C5 = A6 & C6,1,0))>0,0,1)

Basically, the formula is looking for any previous combinations of Service (Col A) and UnavailableFor5Min (Col C) and if it finds one, it considers that it's not the first unavailable component to be unavailable at this time for that same service, so it will return zero. Otherwise, it will return one.

Then, by using the Sum of that variable as the Values for the pivot table, you avoid the double counting.

---

Some more explanations of where the formula comes from:

This formula is an array formula in the sense that the comparison $A$1:A5 & $C$1:C5 = A6 & C6 is performed element-wise and returns an array containing values of true or false, one for each row in $A$1:A5 & $C$1:C5, the IF in this IF($A$1:A5 & $C$1:C5 = A6 & C6,1,0) changes this array to an array of 0s and 1s. With the SUM, we make sure that all of the values are 0, only then we return 1.