Weird Sumproduct Result

[mahmed1]

Hi

I have this formula

=SUMPRODUCT((--((Sheet1!$A$3:$A$25+0)=DASHBOARD!$A$2))*(--(Sheet1!$B$3:$B$25>"")))

This works fine until...

(--(Sheet1!$B$3:$B$25>"")) - I though this would give me anything that is not blank whether that means text or number as it is greater than blank however it seems to ignore numbers as greater than blank and returns false so if i had 9 this returns false =B4>""

Should this be the case ? and what do i need to add to make sure anything other than blank should return a true?

Thank You

 

[Noodleski]

Blank is a string, asfaik. So how should Excel calculate what is greater than? Did you mean different from blank: <>"" or larger than 0?

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(--(Sheet1!$A$3:$A$25+0=DASHBOARD!$A$2),1-(Sheet1!$B$3:$B$25=""))

 

[Noodleski]

Try...

=SUMPRODUCT(--(Sheet1!$A$3:$A$25+0=DASHBOARD!$A$2),1-(Sheet1!$B$3:$B$25=""))

Hi Aladin, just for my own education:

the 1-(Sheet1!$B$3:$B$25="") part has more or less the same function as (Sheet1!$B$3:$B$25<>"").
Having seen your tendency to go for the faster options, does that mean your solution offers a better performance or is there a functional difference I'm missing?

 

[Aladin Akyurek]

Hi Aladin, just for my own education:

the 1-(Sheet1!$B$3:$B$25="") part has more or less the same function as (Sheet1!$B$3:$B$25<>"").
Having seen your tendency to go for the faster options, does that mean your solution offers a better performance or is there a functional difference I'm missing?

I did not profile the '1 minus relational test' bit I often use. (I need an updated version of Wiliams's Fast Excel for the older version no longer works, I guess, on newer systems.) Would feel lucky if it was evaluated faster than the expression with a negation test.

 

[Noodleski]

Thanks for the feedback, Aladin.

 

[mahmed1]

Thank you

 

[Aladin Akyurek]

Thank you

You are welcome.