Styjun

Have there ever been other TV shows or Films that told a similiar story to the new 90210 show?

Quick destruction of a helium filled airship?

May the tower use the runway while an emergency aircraft is inbound?

How does the Moon's gravity affect Earth's oceans despite Earth's stronger gravitational pull?

Attacking the Hydra

How to render "have ideas above his station" into German

What should I do with the stock I own if I anticipate there will be a recession?

Setting up a Mathematical Institute of Refereeing?

How would armour (and combat) change if the fighter didn't need to actually wear it?

Output with the same length always

What is the fastest way to level past 95 in Diablo II?

Is this bar slide trick shown on Cheers real or a visual effect?

Units of measurement, especially length, when body parts vary in size among races

Knights and Knaves on a (Not So) Deserted Island

Why should I pay for an SSL certificate?

Will Force.com stop working on salesforce Lightning?

The space of cusp forms for GL_2 over F_q(T)

Why is the battery jumpered to a resistor in this schematic?

Are there any rules on how characters go from 0th to 1st level in a class?

Weird resistor with dots around it on the schematic

Why do we use low resistance cables to minimize power losses?

What allows us to use imaginary numbers?

What modifiers are added to the attack and damage rolls of this unique longbow from Waterdeep: Dragon Heist?

What are some tips and tricks for finding the cheapest flight when luggage and other fees are not revealed until far into the booking process?

How do I solve the Excel sumproduct formula to get weeknumbers from dates working

Stop Excel from automatically converting certain text values to datesHow to solve mixed date formatsExcel formula to calculate date difference in years and monthsExcel formula sumproduct consuming more time (EPPlus)Excel Formula to sum value if date falls in particular monthExcel - how to get if a date is a specific day of the week?Excel Formula SUMIFS on date and time using date from given cellCopy pasteing concatenated date time values from Excel in the same format as it's seen in ExcelUnable to correctly parse a date from Excel cellExcel Formula to Sum Values between Dates

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

1

An Excel question.

I got a table with a column of dates and a calculated column with value's. In a summary cell I do have

 =SOMPRODUCT( (MONTH(Tijd[Datum(s)])=MONTH(TODAY()) )* (Tijd[Uren gewerkt in decimalen]) )

of course works perfect. However with the same columns and calculating the weekly based summary I get error's

 =SOMPRODUCT( (WEEKNUMBER(Tijd[Datum(s)])=WEEKNUMBER(TODAY()) ) * (Tijd[Uren gewerkt in decimalen]) )

This gives me a #Value err.
If I use WeekNumber(AnEmptyCell) as an test, it just returns a zero, no err value.

F9 key results -

Tijd[Datum(s)] - all the dates and zero's when no date.

(WEEKNUMMER(Tijd[Datum(s)]) - #Value error

all the other parts are also okay.

All the fields in the Datums Column are date fields.

What do I wrong and how to solve this?

excel sumproduct

share|improve this question

asked Mar 27 at 12:29

Lord AnubisLord Anubis

4388 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It was easier to answer the question if you used English formulas. You can get it if you select the target cell, press Alt + F11, Ctrl + G and write the following VBA command: ? ActiveCell.Formula
  
  – z32a7ul
  Mar 27 at 12:59
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Nice tip, However for on the Mac you have to use the 'Fn' + 'Alt' + 'F11' . Then the Ctrl + CMD + G. Thank you
  
  – Lord Anubis
  Mar 27 at 19:30
  

  
  
  
  

add a comment | 

1

An Excel question.

I got a table with a column of dates and a calculated column with value's. In a summary cell I do have

 =SOMPRODUCT( (MONTH(Tijd[Datum(s)])=MONTH(TODAY()) )* (Tijd[Uren gewerkt in decimalen]) )

of course works perfect. However with the same columns and calculating the weekly based summary I get error's

 =SOMPRODUCT( (WEEKNUMBER(Tijd[Datum(s)])=WEEKNUMBER(TODAY()) ) * (Tijd[Uren gewerkt in decimalen]) )

This gives me a #Value err.
If I use WeekNumber(AnEmptyCell) as an test, it just returns a zero, no err value.

F9 key results -

Tijd[Datum(s)] - all the dates and zero's when no date.

(WEEKNUMMER(Tijd[Datum(s)]) - #Value error

all the other parts are also okay.

All the fields in the Datums Column are date fields.

What do I wrong and how to solve this?

excel sumproduct

share|improve this question

asked Mar 27 at 12:29

Lord AnubisLord Anubis

4388 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It was easier to answer the question if you used English formulas. You can get it if you select the target cell, press Alt + F11, Ctrl + G and write the following VBA command: ? ActiveCell.Formula
  
  – z32a7ul
  Mar 27 at 12:59
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Nice tip, However for on the Mac you have to use the 'Fn' + 'Alt' + 'F11' . Then the Ctrl + CMD + G. Thank you
  
  – Lord Anubis
  Mar 27 at 19:30
  

  
  
  
  

add a comment | 

An Excel question.

I got a table with a column of dates and a calculated column with value's. In a summary cell I do have

 =SOMPRODUCT( (MONTH(Tijd[Datum(s)])=MONTH(TODAY()) )* (Tijd[Uren gewerkt in decimalen]) )

of course works perfect. However with the same columns and calculating the weekly based summary I get error's

 =SOMPRODUCT( (WEEKNUMBER(Tijd[Datum(s)])=WEEKNUMBER(TODAY()) ) * (Tijd[Uren gewerkt in decimalen]) )

This gives me a #Value err.
If I use WeekNumber(AnEmptyCell) as an test, it just returns a zero, no err value.

F9 key results -

Tijd[Datum(s)] - all the dates and zero's when no date.

(WEEKNUMMER(Tijd[Datum(s)]) - #Value error

all the other parts are also okay.

All the fields in the Datums Column are date fields.

What do I wrong and how to solve this?

excel sumproduct

share|improve this question

asked Mar 27 at 12:29

Lord AnubisLord Anubis

4388 bronze badges

 =SOMPRODUCT( (MONTH(Tijd[Datum(s)])=MONTH(TODAY()) )* (Tijd[Uren gewerkt in decimalen]) )

 =SOMPRODUCT( (WEEKNUMBER(Tijd[Datum(s)])=WEEKNUMBER(TODAY()) ) * (Tijd[Uren gewerkt in decimalen]) )

Tijd[Datum(s)] - all the dates and zero's when no date.

(WEEKNUMMER(Tijd[Datum(s)]) - #Value error

share|improve this question

asked Mar 27 at 12:29

Lord AnubisLord Anubis

4388 bronze badges

share|improve this question

asked Mar 27 at 12:29

Lord AnubisLord Anubis

4388 bronze badges

asked Mar 27 at 12:29

Lord AnubisLord Anubis

4388 bronze badges

asked Mar 27 at 12:29

Lord AnubisLord Anubis

4388 bronze badges

2 Answers
2

active

oldest

votes

0

There are two types of Excel formula functions: those that can be used with arrays and those that cannot. MONTH is of the first type, WEEKNUM is of the second. To test it, put two arbitrary dates into A1:A2, and enter =MONTH(A1:A2) into B1:B2 (Select B1:B2, and press Ctrl + Shift + Enter), and =WEEKNUM(A1:A2) into C1:C2. The first will show valid values but the second will be two #VALUE! errors.

If you use the formula in the above setup, I would recommend, to examine if the date is after the Monday and before the Sunday of the given week.

I don't have access to an Excel with Dutch (?) locale, so I added the English name of the functions to insert in your formula:

 =SOMPRODUCT( ( TODAY()-WEEKDAY(TODAY(),2)+1 <= Tijd[Datum(s)] ) * ( Tijd[Datum(s)] < TODAY()-WEEKDAY(TODAY(),2)+7 ) * (Tijd[Uren gewerkt in decimalen]) )

share|improve this answer

edited Mar 27 at 12:57

answered Mar 27 at 12:48

z32a7ulz32a7ul

2,01311 gold badge1111 silver badges2828 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi thank you, I did use somewhere else already an extra column with week numbers, So I have to use it, the extra column here too. I do understand your formula and save it for later.
  
  – Lord Anubis
  Mar 27 at 13:06
  
  

  
  
  
  

add a comment | 

0

Is it:

WeekNummer() or:
WeekNumber() or:
WeekNum()?

(The function's name is locale dependent)

share|improve this answer

answered Mar 27 at 12:48

DominiqueDominique

2,92066 gold badges2222 silver badges4949 bronze badges

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55377238%2fhow-do-i-solve-the-excel-sumproduct-formula-to-get-weeknumbers-from-dates-workin%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

There are two types of Excel formula functions: those that can be used with arrays and those that cannot. MONTH is of the first type, WEEKNUM is of the second. To test it, put two arbitrary dates into A1:A2, and enter =MONTH(A1:A2) into B1:B2 (Select B1:B2, and press Ctrl + Shift + Enter), and =WEEKNUM(A1:A2) into C1:C2. The first will show valid values but the second will be two #VALUE! errors.

If you use the formula in the above setup, I would recommend, to examine if the date is after the Monday and before the Sunday of the given week.

I don't have access to an Excel with Dutch (?) locale, so I added the English name of the functions to insert in your formula:

 =SOMPRODUCT( ( TODAY()-WEEKDAY(TODAY(),2)+1 <= Tijd[Datum(s)] ) * ( Tijd[Datum(s)] < TODAY()-WEEKDAY(TODAY(),2)+7 ) * (Tijd[Uren gewerkt in decimalen]) )

share|improve this answer

edited Mar 27 at 12:57

answered Mar 27 at 12:48

z32a7ulz32a7ul

2,01311 gold badge1111 silver badges2828 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi thank you, I did use somewhere else already an extra column with week numbers, So I have to use it, the extra column here too. I do understand your formula and save it for later.
  
  – Lord Anubis
  Mar 27 at 13:06
  
  

  
  
  
  

add a comment | 

0

There are two types of Excel formula functions: those that can be used with arrays and those that cannot. MONTH is of the first type, WEEKNUM is of the second. To test it, put two arbitrary dates into A1:A2, and enter =MONTH(A1:A2) into B1:B2 (Select B1:B2, and press Ctrl + Shift + Enter), and =WEEKNUM(A1:A2) into C1:C2. The first will show valid values but the second will be two #VALUE! errors.

If you use the formula in the above setup, I would recommend, to examine if the date is after the Monday and before the Sunday of the given week.

I don't have access to an Excel with Dutch (?) locale, so I added the English name of the functions to insert in your formula:

 =SOMPRODUCT( ( TODAY()-WEEKDAY(TODAY(),2)+1 <= Tijd[Datum(s)] ) * ( Tijd[Datum(s)] < TODAY()-WEEKDAY(TODAY(),2)+7 ) * (Tijd[Uren gewerkt in decimalen]) )

share|improve this answer

edited Mar 27 at 12:57

answered Mar 27 at 12:48

z32a7ulz32a7ul

2,01311 gold badge1111 silver badges2828 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi thank you, I did use somewhere else already an extra column with week numbers, So I have to use it, the extra column here too. I do understand your formula and save it for later.
  
  – Lord Anubis
  Mar 27 at 13:06
  
  

  
  
  
  

add a comment | 

There are two types of Excel formula functions: those that can be used with arrays and those that cannot. MONTH is of the first type, WEEKNUM is of the second. To test it, put two arbitrary dates into A1:A2, and enter =MONTH(A1:A2) into B1:B2 (Select B1:B2, and press Ctrl + Shift + Enter), and =WEEKNUM(A1:A2) into C1:C2. The first will show valid values but the second will be two #VALUE! errors.

If you use the formula in the above setup, I would recommend, to examine if the date is after the Monday and before the Sunday of the given week.

I don't have access to an Excel with Dutch (?) locale, so I added the English name of the functions to insert in your formula:

 =SOMPRODUCT( ( TODAY()-WEEKDAY(TODAY(),2)+1 <= Tijd[Datum(s)] ) * ( Tijd[Datum(s)] < TODAY()-WEEKDAY(TODAY(),2)+7 ) * (Tijd[Uren gewerkt in decimalen]) )

share|improve this answer

edited Mar 27 at 12:57

answered Mar 27 at 12:48

z32a7ulz32a7ul

2,01311 gold badge1111 silver badges2828 bronze badges

 =SOMPRODUCT( ( TODAY()-WEEKDAY(TODAY(),2)+1 <= Tijd[Datum(s)] ) * ( Tijd[Datum(s)] < TODAY()-WEEKDAY(TODAY(),2)+7 ) * (Tijd[Uren gewerkt in decimalen]) )

share|improve this answer

edited Mar 27 at 12:57

answered Mar 27 at 12:48

z32a7ulz32a7ul

2,01311 gold badge1111 silver badges2828 bronze badges

answered Mar 27 at 12:48

z32a7ulz32a7ul

2,01311 gold badge1111 silver badges2828 bronze badges

answered Mar 27 at 12:48

z32a7ulz32a7ul

2,01311 gold badge1111 silver badges2828 bronze badges

0

Is it:

WeekNummer() or:
WeekNumber() or:
WeekNum()?

(The function's name is locale dependent)

share|improve this answer

answered Mar 27 at 12:48

DominiqueDominique

2,92066 gold badges2222 silver badges4949 bronze badges

add a comment | 

0

Is it:

WeekNummer() or:
WeekNumber() or:
WeekNum()?

(The function's name is locale dependent)

share|improve this answer

answered Mar 27 at 12:48

DominiqueDominique

2,92066 gold badges2222 silver badges4949 bronze badges

add a comment | 

Is it:

WeekNummer() or:
WeekNumber() or:
WeekNum()?

(The function's name is locale dependent)

share|improve this answer

answered Mar 27 at 12:48

DominiqueDominique

2,92066 gold badges2222 silver badges4949 bronze badges

WeekNummer() or:
WeekNumber() or:
WeekNum()?

share|improve this answer

answered Mar 27 at 12:48

DominiqueDominique

2,92066 gold badges2222 silver badges4949 bronze badges

answered Mar 27 at 12:48

DominiqueDominique

2,92066 gold badges2222 silver badges4949 bronze badges

answered Mar 27 at 12:48

DominiqueDominique

2,92066 gold badges2222 silver badges4949 bronze badges