Find text and calculate according to Month

**sickreto** · 06-04-2021, 09:34 AM

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.

**FDibbins** · 06-04-2021, 11:32 PM

You give no data to work with, and show no expected outcome? It would help if you provides these, please - that way we have an idea of what you expect.

**sickreto** · 06-06-2021, 05:02 AM

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.

**wk9128** · 06-06-2021, 07:03 AM

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:

**sickreto** · 06-06-2021, 07:40 AM

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.

**sickreto** · 06-06-2021, 08:39 AM

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.

**sickreto** · 06-06-2021, 08:46 AM

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.

**sickreto** · 06-07-2021, 05:29 PM

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

**bebo021999** · 06-08-2021, 12:10 AM

First, change sheet name to Jan, Feb,...(3 letters format)
Sheet summary row 4 is months Jan, Feb,...(3 letters format)
All 12 sheets of month have same margin (Week1 to 4 tables are put in same row, column index).
C6:

=IFERROR(SUMPRODUCT((INDIRECT(B$4&"!$c$23:$c$27")=$B6)*(TEXT(INDIRECT(B$4&"!$D$23:$J$27"),"mmm")=C$4)*(INDIRECT(B$4&"!$D$23:$J$27")="HOL")),0)
+SUMPRODUCT((INDIRECT(C$4&"!$c$7:$c$11")=$B6)*(TEXT(INDIRECT(C$4&"!$D$4:$J$4"),"mmm")=C$4)*(INDIRECT(C$4&"!$D$7:$J$11")="HOL"))
+SUMPRODUCT((INDIRECT(C$4&"!$C$13:$C$27")=$B6)*(INDIRECT(C$4&"!$D$13:$J$27")="HOL"))
+SUMPRODUCT((INDIRECT(C$4&"!$c$23:$c$27")=$B6)*(TEXT(INDIRECT(C$4&"!$D$23:$J$27"),"mmm")=C$4)*(INDIRECT(C$4&"!$D$23:$J$27")="HOL"))
+IFERROR(SUMPRODUCT((INDIRECT(D$4&"!$c$7:$c$11")=$B6)*(TEXT(INDIRECT(D$4&"!$D$4:$J$4"),"mmm")=C$4)*(INDIRECT(D$4&"!$D$7:$J$11")="HOL")),0)

For specific month, i.e, Feb, there are 5 SUMPRODUCTs which:
1st, count feb days in week 4 of previous month Jan
2nd, count feb days in week 1 of feb
3rd, count feb days in week 2+3 of feb
4th, count feb days in week 4 of feb
5th, count feb days in week 1 of next month Mar.

**sickreto** · 06-08-2021, 07:02 PM

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.

**bebo021999** · 06-08-2021, 09:23 PM

Originally Posted by sickreto

the month of July and August is not giving me a correct calculation unlike the months of Jan to Jun.

Originally Posted by bebo021999

First, change sheet name to Jan, Feb,...(3 letters format)
Sheet summary row 4 is months Jan, Feb,

Are you using July and August? make sure months is both sheets are the same (Jul,Aug...with 3 letters)

**sickreto** · 06-09-2021, 08:07 AM

Hi,

Yes. I'm using the 3 letters to all months names on all sheets. Anything else I'm missing?

It's giving results but it's capturing both months. Say Jun 30 and Jul 1, they are in the same week, so if I put HOL then it gives me a total of 2 rather than 1 one ache month( 1 Jun and 1 for Jul).

Thank you.

**bebo021999** · 06-09-2021, 09:40 PM

Upload the file again with the issue

**sickreto** · 06-10-2021, 04:54 AM

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.

**bebo021999** · 06-10-2021, 05:12 AM

Every month's days are fixed with 4 weeks * 7 = 28 days.

Then Jun ends with 20-Jun

Then 1st week of Jul is from 21-27-Jun-21 (???)

and Dec, 1st 3 weeks belong to Nov, and Dec end with 5-Dec.

**sickreto** · 06-10-2021, 05:25 AM

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.

**bebo021999** · 06-10-2021, 05:39 AM

Is it possible to arrange week# come accross columns like this:
If not, VBA may be a good choice.

**sickreto** · 06-10-2021, 06:15 AM

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

Find text and calculate according to Month

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Find text and calculate according to Month

Re: Find text and calculate according to Month

Re: Find text and calculate according to Month

Re: Find text and calculate according to Month

Re: Find text and calculate according to Month

Re: Find text and calculate according to Month

Re: Find text and calculate according to Month

Re: Find text and calculate according to Month

Re: Find text and calculate according to Month

Re: Find text and calculate according to Month

Re: Find text and calculate according to Month

Re: Find text and calculate according to Month

Re: Find text and calculate according to Month

Re: Find text and calculate according to Month

Re: Find text and calculate according to Month

Re: Find text and calculate according to Month

Re: Find text and calculate according to Month

Re: Find text and calculate according to Month

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Need formula to calculate days used in month initiated in previous and selected month

[SOLVED] Calculate a YTD Total based on Reporting Month and Week of Month via drop down selections

[SOLVED] Find a month in text and display that in next column

Macro to calculate Month to year sales based on month selection

[SOLVED] Macro to find, replace text and calculate VAT in a cell

[SOLVED] How to find the 'next month' given a starting month as text?

Find text in a column and calculate numbers in the same row

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions