Sumproduct

[mr_baz]

Hi all,

I can't find what is wrong with my formula...

=SUMPRODUCT((ThisWeek<ToDoGX56<=NextWeek)*(TYPEGX56="Fast")*(ToDoGX56<>""))

This week and NextWeek are YYYY,MM,DD as usual.

Does anyone see anything wrong?

Thx!

 

[barry houdini]

Hello mr baz.......your formula is corrupted when viewed normally although I can see it as a "tooltip" when I hover on your thread title.

You are trying to use a "between" type operation like

1< A1:A10< 10

.....you can't do that - split it into 2 tests, i.e.

1< A1:A10 and

A1:A10 < 10

 

[baitmaster]

um, yes..?

What exactly are you trying to do? Is thisWeek a named range? and what about nextWeek...?

EDIT: ah yes, the old "html removing diagonal brackets" wheeze...

 

[mr_baz]

sorry, the formula was incomplete.

=SUMPRODUCT((ThisWeek<ToDoGX56<=NextWeek)*(TYPEGX56="Fast")*(ToDoGX56<>""))

What I what to do is to check date between a date(today) and the next week(Today+7).

I tried several formulas and never get a good result.

I also tried =SUMPRODUCT((ThisWeek<ToDoGX56, ToDoGX56<=NextWeek),(TYPEGX56="Fast"),(ToDoGX56<>""))

Thanks again

 

[mr_baz]

first
SUMPRODUCT((ThisWeek<ToDoGX56<=NextWeek)*(TYPEGX56="Fast")*(ToDoGX56<>""))

second
SUMPRODUCT((ThisWeek<ToDoGX56, ToDoGX56<=NextWeek),(TYPEGX56="Fast"),(ToDoGX56<>""))


<todogx56<=nextweek)*(typegx56="fast")*(todogx56<><todogx56, todogx56<="NextWeek),(TYPEGX56="Fast"),(ToDoGX56<"></todogx56,></todogx56<=nextweek)*(typegx56="fast")*(todogx56<>

 

[Jonmo1]

something's wrong, my formulas doesn't appear completely in the post

first:
SUMPRODUCT((ThisWeek<TODOGX56<=NEXTWEEK)*(TYPEGX56="FAST")*(TODOGX56<>""))

second:
SUMPRODUCT((ThisWeek<TODOGX56, ToDoGX56<='NextWeek),(TYPEGX56="Fast"),(ToDoGX56<'>""))

Hope will work this time

Put spaces before and after each < and > symbol
The forum software thinks they are HTML code, and it breaks it up..

 

[mr_baz]

thanks!

again!

first
SUMPRODUCT((ThisWeek < ToDoGX56 < = NextWeek)*(TYPEGX56="Fast")*(ToDoGX56 < > ""))

second
SUMPRODUCT((ThisWeek < ToDoGX56, ToDoGX56 < =NextWeek),(TYPEGX56="Fast"),(ToDoGX56 < > ""))

Both doesn't work

 

[baitmaster]

depending what your named ranges are, you probably need to enforce arrays to be returned within your calculations using --(xxx) around each set of questions

see http://www.xldynamic.com/source/xld.SUMPRODUCT.html for detailed overview of Sumproduct

e.g. =SUMPRODUCT(--(ThisWeek < ToDoGX56), --(ToDoGX56 < =NextWeek),--(TYPEGX56="Fast"),--(ToDoGX56 < > ""))

 

[mr_baz]

depending what your named ranges are, you probably need to enforce arrays to be returned within your calculations using --(xxx) around each set of questions

see http://www.xldynamic.com/source/xld.SUMPRODUCT.html for detailed overview of Sumproduct

e.g. =SUMPRODUCT(--(ThisWeek < ToDoGX56), --(ToDoGX56 < =NextWeek),--(TYPEGX56="Fast"),--(ToDoGX56 < > ""))

Thank you all

and thanks for the links very helpful!