Using a distinctcount of days within a week as an operator in a formula within Pivot-Table

[Kaisan]

Hi.
I am using MS Excel 2016
I've done some searching in the forums, but I can never seem to find something quite similar to my problem.

I have a dataset consisting of several entries divided amongst a couple of cars per day.

The data includes:

• Entry-number
• Date
• Car used
• Timestamps for each status change of the journey
• + a ton of irrelevant data to my question.

I want to make a report of the effective usage of each car. And I want this as a percentage of time available for the different cars. The availability varies from car to car.

So far I've made a pivot-table (which is great for displaying relevant information), but I am stumped as to how I can make a weekly report for the coverage of the cars. The table adds up the different entries for each day rather beutifully, and with the use of IF-formulas, I've managed to calculated the time in service divided by available time.

My problems arise when I try to consolidate this in periods of one week. The calculations are a simple thing to alter, but if there are days where the car has not been available during a certain week, my current formulas are not taking those into account, and I get a faulty coverage-report.

Does anyone know a way I can distinctly count the number of unique days within a period in a pivot-table and then use the distinct count within a field-calculation to get the right number of days in my formulas divider?
Alternatly, if anyone has a better idea of how to approach the problem, I am open to suggestions.



with regards
Kai

[davsth]

Sometimes a column of 1/countifs(…..) will suffice

if a is car and b is date and a car was driven 3 times on a day 1/countifs(a:A,a1,b:B,b1) will give a third, but will sum to 1 in other calculations


Without an attachment it is hard to be specific

[Kaisan]

I will try this when I am able. Thank you.
I am guessing countif on dates will function much in the same way

[davsth]

Yes but you may wish to not include the dates, as you would wish this number to be the full number of days and it will appear in your pivot table anyway