Sum Product is showing 0 when i use f9 to evaluate, it shows 1.

My sumproduct formula is

Formula:

=SUMPRODUCT(--(IFERROR(WEEKNUM(LEFT(C1:C3,(SEARCH("-",C1:C3,1)-2)),2)=WEEKNUM(A1,2),0)))

the purpose is to sum values if it occurs on the same week as the week put into A1 The cells C1:C3 are a string in the format "d/m/yy - d/m/yy" that is why I have the left and search. I left out the values because I believe the issues is with this portion. Whenever I use f9 to evaluate the formula, I get a count of 1 if i have 1 instance, but letting the formula run gives me 0. Capture.PNG




Solved. I wasn't confirming with CTRL + SHIFT + ENTER

Thanks for all of the help guys!

Here's an ARRAY formula (applied by pressing Ctrl + Shift + Enter) that seems to work:

Formula:

=SUM(IFERROR(IF(WEEKNUM(A1,2)=WEEKNUM(LEFT(C1:C3,FIND("-",C1:C3)-2),2),1,0),0))

I don't use SUMPRODUCT much, so not sure why that is not working.

- Moo

It works perfectly fine on my end. Not quite sure what is happening on yours.

- Moo

I attached a file that shows it working just fine, using ARRAY formula entry (Ctrl + Shift + Enter) instead of just Enter, as I stated last night. It works.

- Moo

Yes, it does. I've used SUMPRODUCT sparingly in the past, and have always used it as an alternative to ARRAY formulas, so I didn't even think to try it, but as my attached file shows, it does.

No problem, jeeke. Glad to help, and thanks for the feedback! Have a great day!

- Moo