CountIF Integrated in Getpivotdata

[jzosopp]

Dear all,

I'm new here and decided to join as I'm always able to find some great answers in this forum. However, I'm racking my brain to try to figure out an easy approach to getting a countif nested in a getpivotdata or similar and haven't found anything.

I'm trying to count the amount of "shifts" that fall between intervals. My pivot looks similar to below (maybe remove End Shift Time and include duration of shift...):

ID DD/MM/YY
Start Shift Time End Shift Time
1 9:00 17:30
2 12:30 21:00
3 8:00 16:30
4 8:00 16:30
5 9:00 17:30
6 10:30 19:30


My intervals are as below:
7:00:00
7:30:00
8:00:00
8:30:00
9:00:00
9:30:00
10:00:00
10:30:00
11:00:00
11:30:00
12:00:00
12:30:00
13:00:00
13:30:00
14:00:00
14:30:00
15:00:00
15:30:00
16:00:00
16:30:00
17:00:00
17:30:00
18:00:00
18:30:00
19:00:00
19:30:00
20:00:00
20:30:00


I managed to use the following formula to get the data I want: =COUNTIF('Shift StartEND Pivot'!HZ$8:HZ$152,">"&$B3)-COUNTIF('Shift StartEND Pivot'!HY$8:HY$152,">"&$B3)

However, this is time consuming as I need to do this for each day for a total of 3 months at a time. Attached example.

Thank you in advance for help!

Best,
JZoSoPP

[shivya]

Mention your desired result as well.

[jzosopp]

I need to figure out the count of shifts per interval per day for 3 months.

E.g. On Monday the 30th of October, I have 3 shifts starting at 9:00 to 5:30pm and 1 shift starting at 12:30 - 9:00pm - then count how many of these shifts are in each 30min interval. In this example, I should get 3 counts at 9AM, 3 counts at 9:30AM......4 counts at 12:30pm......1 count at 5:30pm...

The Countif formula works, but its not efficient when running on a 3month time range, and when you have different services.

I hope this clarifies

[jzosopp]

Anyone have any leads?

[shivya]

Hello jzosopp,

In which column you are trying to place your formula for 30th Oct. We are not clear Please mention your desired result in sheet.

[JeteMc]

Hello jzosopp and Welcome to Excel Forum.
This is a low tech proposal.
Put a helper table at the bottom of the 'Pivot Values' sheet.
Column A of the table would be populated by the time intervals.
Columns B:IU would be populated by the formula:

=IF(C$6="",COUNTIF('Pivot Values'!C$9:C$76,">"&$A82)-COUNTIF('Pivot Values'!B$9:B$76,">"&$A82),"")

On the 'Actual Staff' sheet the values are taken from the helper table using the formula:

=INDEX('Pivot Values'!$B$82:$IU$109,MATCH($B2,'Pivot Values'!$A$82:$A$109,0),MATCH(C$1,'Pivot Values'!$B$6:$IU$6,0))

I imagine that some modification will need to be made to accommodate the different types of staff (GNR ESP, GNR FRE etc.) however perhaps this will get you started.
Let us know if you have any questions.