CODEWITHSUNDEEP
exceldateformula
user4373888
user4373888

Reputation: 63

Formula's with year and weeknumber

I want to create a SUMIFS formula with a date criteria. The whole report is based on years and weeknumers and I like to keep it that way without converting these to dates. The data is set up as following:

Col A   Col B      Col C     Col D
YEAR    Weeknumber  Turnover  ColA & ColB

I want to create a MAT turnover number based on the report week and year. I tried to merge colums A & B to get YYYYWW but since the first 9 weeks are only one digit the formula does not work.

note Cel F1 contains the report year and Cel F2 contains the report week

The sumif formula is the folowing: =SUMIFS(C:C;D:D;"<="&F1&F2;D:D;">="&(F1-1)&F2)

Anyone got any idea how I can make this work?

Upvotes: 0

Views: 59

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

So your column D formula:

=--(A1&TEXT(B1,"00"))

Your other formula:

=SUMIFS(C:C,D:D,"<=" & --(F1 & TEXT(F2,"00")),D:D,">=" & --(F1-1 & TEXT(F2,"00")))

If you want to do it all with one formula then:

=SUM(IF(( --(F1 & TEXT(F2,"00"))>=--(A:A &TEXT(B:B,"00")))*( --(F1-1 & TEXT(F2,"00"))<=--(A:A &TEXT(B:B,"00"))),C:C))

This is an array formula and needs to be confirmed with Ctrl-Shift-Enter when leaving edit mode.

This is slow as it is written. It would be best if all the full column references were truncated to just the extreme absolute data limit. As in A:A to $A$1:$A$10000. This will speed up the array formula.

Upvotes: 1

Related Questions