• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Three Criteria Table Referencing

wizpeter

New Member
Hi,

I've been banging my head against a wall with this and I'm reaching out to the internet for some wisdom and help.

I have a basic spreadsheet that shows a request for the cover of hours which is then either then picked up by Group 1, Group 2 or Group 3.

What I need to do is show in the report a summary of the requests for hours covered in each four weekly period along with the hours that are/were outstanding. I originally tried a pivot table which I can do for a single date range but I understand it isn't possible to show multiple date ranges in one pivot table, the use of multiple pivot tables isn't practical as I have a number of areas that I need to duplicate the below for.

I've tried COUNTIFS and IF statements referencing the start and end date periods in column A and B but I just can't get it to work, although I suspect its a three stage formula - if a date between column A and B and in e.g. group 1 then count hours in hours column. It also needs count the entire column in the data tab, the data tab is formated into a table (called Table1 for reference).

Many thanks for any help.

Report Tab

upload_2015-11-22_20-53-9.png

Data Tab

upload_2015-11-22_20-54-39.png
 

Attachments

  • upload_2015-11-22_20-51-44.png
    upload_2015-11-22_20-51-44.png
    138.4 KB · Views: 2
Hi:

I guess you can use sumproduct formula to achieve this could upload a sample workbook?

Thanks
 
I didn't think of using a sumproduct, I've had a play around with that but I still can't get it to work.
 

Attachments

  • Working Hours.xlsx
    117.4 KB · Views: 3
Back
Top