Sumif week number within a specific month

[Tatum2020]

Hi everyone,

I hope you are keeping safe.
I am trying to calculate the revenue generated for a specific week based on monthly revenues. Is there a formula I can apply to do this? (i.e. I am trying to calculate the weekly revenue generated)

Input Table is set up like this:

	Monthly revenue
	Jan-21	Feb-21
Joe	1,000	0.00
Ben	400	0.00
Kate	200	600
Sam	700	1,500

Output table:

Weeks	1	2	3	4	5
Monthly revenue

 

[jasonb75]

Is there a formula I can apply to do this?

There is most probably one that you could use, but it will be specific to your requirement rather than a simple 'off the shelf' formula, we would need more (accurate) information in order to provide the correct one.

Are you saying that if a week consists if the last 3 days of Jan and first 4 days of Feb that the result should be (Jan revenue / 31) *3 + (Feb revenue / 28) * 4 ? If not then what should it be?

What should be the start and end point of the week for the purpose of the formula?

Which week numbering method should be used, (does week 1 start on Jan 1st, is it the first week that has 4 or more days in Jan, or something else)?

 

[Tatum2020]

There is most probably one that you could use, but it will be specific to your requirement rather than a simple 'off the shelf' formula, we would need more (accurate) information in order to provide the correct one.

Are you saying that if a week consists if the last 3 days of Jan and first 4 days of Feb that the result should be (Jan revenue / 31) *3 + (Feb revenue / 28) * 4 ? If not then what should it be?

What should be the start and end point of the week for the purpose of the formula?

Which week numbering method should be used, (does week 1 start on Jan 1st, is it the first week that has 4 or more days in Jan, or something else)?

Hi Jason,

Thank you for the response.

1. That is correct - it should be (Jan revenue / 31) *3 + (Feb revenue / 28) * 4
2. Start and endpoint should be Monday and Sunday
3. Week 1 should start on the 1st of January

I hope that answers your questions.

 

[jasonb75]

I hope that answers your questions.

So just to clarify on point 3, week 1 for 2021 should only be 3 days (Friday - Sunday), and the last week of December should be 5 days (Dec 31 is a Friday)?

I'm going to be afk for the rest of the day, I expect that some of the other board regulars will pick up your thread before I return. I'll check when I return and post a formula for you if it has not already been resolved.

 

[jasonb75]

I haven't forgotten about your question, this is proving more of a challenge than I first thought it would be.

 

[jasonb75]

See if this will do what you need, note that it will not work if there are 2 rows with the same name in the input table.

This formula will only work with a recently updated version of office 365, it might be possible to reproduce the formula with older versions that don't have the functions used, but it would not be practical.

sumif weeknum.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1		Monthly revenue
2		Jan-21	Feb-21
3	Joe	1,000	-
4	Ben	400	-
5	Kate	200	600
6	Sam	700	1,500
7
8
9	Weeks	1	2	3	4	5	6	7	8	9	10
10	Joe	96.774194	225.80645	225.80645	225.8065	225.8065	0	0	0	0	0
11	Ben	38.709677	90.322581	90.322581	90.32258	90.32258	0	0	0	0	0
12	Kate	19.354839	45.16129	45.16129	45.16129	45.16129	150	150	150	150	0
13	Sam	67.741935	158.06452	158.06452	158.0645	158.0645	375	375	375	375	0
Sheet6

Cell Formulas
Range	Formula
B9:BB9	B9	=SEQUENCE(,WEEKNUM(EOMONTH($B$2,11),2))
A10:A13	A10	=UNIQUE(A3:A6)
B10:K13	B10	=LET(s,$B$2,e,EOMONTH(s,1),q,SEQUENCE(e-s+1,,s),w,WEEKNUM(q,2),m,EOMONTH(q,-1)+1,d,DAY(EOMONTH(q,0)),r,INDEX($B$3:$C$6,MATCH($A10,$A$3:$A$6,0),0),SUMPRODUCT(($B$2:$C$2=m)*(B$9=w)*(r/d)))
Dynamic array formulas.

 

[Tatum2020]

Thank Jason, it worked

So just to clarify on point 3, week 1 for 2021 should only be 3 days (Friday - Sunday), and the last week of December should be 5 days (Dec 31 is a Friday)?

I'm going to be afk for the rest of the day, I expect that some of the other board regulars will pick up your thread before I return. I'll check when I return and post a formula for you if it has not already been resolved.