Total Cells if within a Certain Date

[tomskelly89]

Looking for some help!

I have a list of Employee ID's and need to total the number of days in column D (per employee ID) if the dates provided are in July.

Attached is the sample workbook - any help would be greatly appreciated!

I realise that you will have to summarise the data in a pivot table but can't work out the calculations.

[AliGW]

Which version of Excel are you using?

[tomskelly89]

Hi AliGW - i'm using office 365

[AliGW]

Thanks - please update your forum profile.

[AliGW]

Here is a PowerQuery solution.

M Code:

Please Login or Register  to view this content.

[tomskelly89]

Thanks AliGW - will update my profile - if I wanted to do this for August, September etc once the dates are populated is there an easy way to do this?

[AliGW]

Yes - you just change the month or months that are filtered in the query.

Alternatively, leave the query unfiltered, then add a sub-totals line to the table and filter that.

[AliGW]

Please update your profile, as requested.

Attached is the alternative suggestion I made in my previous post.

[tomskelly89]

thanks AliGW - is there an easy way to add this to another file that I pull a report from?

[AliGW]

Yes. First, import the data from the other workbook using Get Data | From File | From Workbook. Now you can copy the code from the original query into the new one. This assumes that the data layout will be the same. The query isn't hard to learn - just step through the steps in the query editor to work out how it's done.

[tomskelly89]

not sure this solution will work for me as i will need to input this solution into my other workbook rather than this way round - do you know of any other way?

[AliGW]

Not a problem! In the other workbook, load the table to PQ in the same way and use the code I have given you - just tweak it as necessary.