Average using multiple criteria with start and end date

[jayp_]

Hi,

I am not sure if its right to post it here or in the formulas section, but here is what i am trying to do.
I have 2 sheets, Sheet "BillabilityProductivity Report" shows the billability percentage per month and the total productivity per month. All figures in the per month should come from the Resource Management Sheet.

1. Say for the months of June and July in the Billability for Regie, its values will come from the second sheet with the column C filtered with Billable and the date range should be within june and july, and the values to average is Column BD for the billable criteria.

2. Productivity on 1st sheet will total the billable and non billable for a given month.

3. I should also be able to add entries for the projects/campaigns and select billable or not and should reflect in 1st sheet.

I am having issues with the formula that'll add the date range as a criteria. I attached a sample excel.RM1.xlsx

[hoyasaxa215]

I'm not sure your data is organized in the most convenient manner given your objectives. It'd probably be easiest to track billability for everyone on a daily basis, which would be much easier than your start and end dates when it comes to calculating monthly percentages. Can you reorganize the data (or do you possibly have it on a daily basis elsewhere)?

[jayp_]

Thanks for the reply, however we dont have a daily track of billability/non bill. we just have a start date and end date to show

I just need the following criteria to be looked at in the 2nd sheet:
1. Column C (billable/ non-billable)
2. end date and start date
3. column BC

I tried the averageifs with ">= [date]" and "<=[date]" criteria but its not working.

Do you have any suggestion on how to go about with this?

appreciate your help.

[hoyasaxa215]

I'm not sure how to create monthly subtotals using date ranges. Maybe post in the VBA or Formulas forums and maybe someone will know how. Sorry.

[jayp_]

thanks mate. cheers.