# How can I calculate per hour, per day volumes if one or multiple days' data is selected?

#### louwen

##### New Member
hi. I have a scenario where i need to calculate the hourly and daily volume work effort of staff using specific criteria such as the days are 7 hours and days are 21 days in the month.

Easy enough i know, except i am using SLICERS to select the relevant month as well as the specific day in that month. This means that if I select one day only, the day data should only show that day and obviously the hourly average. But if I select multiple days in my SLICER I need some sort of formula to step in and then re-calculate the averages accordingly knowing that more than one day has been selected?
Below is an example of what I am looking at;

The Volume is for two days as selected via SLICER.

 A B C Actual Volume Per Hour (7 hrs) Per Day (21 days) 15

<tbody>
</tbody>

### Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
What is your Slicer controlling?

Hi. Controlling multiple Pivot sheets that are linked to a database. Made them to keep users from having to filter on multiple sheets and by accident mess the whole process up.

Last edited:
ADVERTISEMENT
I do have GETPIVOTDATA formulas, yes. they help consolidate some data onto this front sheet. however, in this case i use them to consolidate the relevant data to another sheet an from that sheet do a VLOOKUP to this Column.

So can you use it to count the days?

ADVERTISEMENT
Hi. Unfortunately the days or months data are from a live database. so no. I can only select the specific day eg Monday September 15 2014 as part of the filter on the pivot.

Can't you create a pivot table (linked to your Slicer) like this to count the dates?

Excel 2010
ABCDEF
1DateRow LabelsCount of Date7
201/09/201401/09/20141
302/09/201402/09/20141
403/09/201403/09/20141
504/09/201404/09/20141
605/09/201405/09/20141
706/09/201406/09/20141
807/09/201407/09/20141
9Grand Total7
Sheet6
Cell Formulas
RangeFormula
F1=GETPIVOTDATA("Date",\$C\$1)

hi. Not sure if this will help, as it will need to be done for more than a year worth of dates as the data available goes back 3 years.

I don't see how the number of dates makes any difference.

Replies
3
Views
436
Replies
3
Views
589
Replies
1
Views
925
Replies
9
Views
716
Replies
6
Views
897

Threads
1,196,235
Messages
6,014,146
Members
441,807
Latest member
sjkenjalo

### 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 MrExcel.com.
Allow Ads at MrExcel

### 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 "mrexcel.com".
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