Find text and calculate according to Month

Hi,

I have a workbook that has a drop down list option for each name.

What I'm trying to achieve is to create a summary of what was given to each name according to Months.

But the preferred way to put the dates is according to number of weeks. As we all know that some months may start at the middle of the week.

I need to create a summary for each individual.

Say Name 1 have a total of 5 Hol in Jan, Name 2 have a total of 3 Hol in Jan and so on.

Find function is perfect but how can I make sure that the required falls on a Month instead of Week to each individual?

Any help is appreciated and thank you very much in advance.

I have my Workbook attached.

Hi,

Thank you.

I have attached the workbook with data of what I am trying to achieve.

I manually enter the number of Holidays to each month on a Summary sheet by getting the data from the week's sheet.

The summary should calculate the number of Holidays. The problem I have is, the data where I get them is on a week's format which an end date of the month will be followed by the start date of the new month, please see Week 13-16 Sheet (name 1) where March 31 is followed by April 1 and they both fall on Week 13 of the year.

Instead of manually entering a total of Holidays on the Summary Sheet (name 1), it will automatically add up all the Holidays as they entered by the user.

I hope this makes sense. And again thank you very much.

First change the name of the worksheet as below
Week 1 - 4 >>>> Jan
Week 5-8 >>>> Feb
Week 9-12 >>>> Mar
Week 13-16 >>>> Apr

Seond change the name as below (Because EXCEL is not smart, can't distinguish colors and have the same name unless use VBA code)
name 1
name 2
name 3
name 4
name 5


worksheet name : Summary
cell C6 formula , Drag down and accross

HTML Code: 

Hi wk9128,

Thank you.

But just wondering the result doesn't change when I enter data.

Tried to change few things in the formula but can't get it.

Hi,

I got it. The only error now I'm getting is not giving me a correct total sum of HOL.

What I wonder what I'm missing.

Thank you.

Hi,

I think what happened here is data are being captured according to by month. But what I'm trying to achieve is the same in exception of the Month to Month. What I'm trying to achieve is from Weeks going to Month.

I need to calculate the HOL given on a specific date captured from the formatted weeks and bring the result to a summary but grouped in Months.

Thank you.

Hi,
Should a Match formula work in here? Any thoughts please. I’m really lost in this one. Appreciate your help. Thank you

Hi,

Thank you very much for giving time to share your knowledge.

It is quite a complex formula I should say and thank you for that. May I ask please, the month of July and August is not giving me a correct calculation unlike the months of Jan to Jun.

I'm not sure which area of the formula needs changing, I have tried moving around some but nothing seems to work.

Thank you.

Hi,

I have attached the file where I have filled the end of Jun and early Jul with Summary having for Jul only and none for Jun.

Also at the end of Dec I can't add more due to Dec being taken already as a name in the sheet, because of this I can't add the last few weeks of the year. Any advise please??

Thank you very much.

Hi,

Yes. As I need to go along by Weeks of the Year rather than Months then calculating in a Summary sheet the Totals according to Months. Sorry if I have confuse you.

According to 2021 Calendar the 1st week of the year is Jan 4 so when I put in Week 1 then it will start on Jan 4 2021 then it will continue along as I put in by Weeks number. Then I created a Summary where it will capture all HOL in total but gathering all of those data (HOL) according to Month.

Hope this make sense. I'm open for other approach on this one.

Thank you.

There's no image attached. I'm willing to try all other ways.