CODEWITHSUNDEEP
excelsumproduct
mcadamsjustin
mcadamsjustin

Reputation: 361

Use SUMPRODUCT when field is blank from formula and dates

I have two columns in my excel, Date 1 in A and Date 2 in B. I'm trying to find the number of times Column B is greater than Column A. I'm using the formula =SUMPRODUCT(((B2:B5-$A$2:$A$5)>0)*1) and receiving an error. The error is due to the data in Column B is being pulled in from a formula, where my value_if_false is "". While the cell is blank, Excel sees this as data and will not execute my original formula.

Blank Error

If I go to B4 and delete the value, my original SUMPRODUCT will execute. I'd like not have to go back and do that. I've tried =SUMPRODUCT((NOT(ISBLANK(B2:B5-$A$2:$A$5)>0))*1) but it's returning 0. Any suggestions?

Upvotes: 0

Views: 624

Answers (3)

Tom Sharpe
Tom Sharpe

Reputation: 34420

I guess if you wanted to stick with SUMPRODUCT and avoid array entry, you could try

=SUMPRODUCT((B2:B5<>"")*(B2:B5>A2:A5))

or to exclude anything except a number (which in this case would be formatted as a date)

=SUMPRODUCT(ISNUMBER(B2:B5)*(B2:B5>A2:A5))

is possibly better.

enter image description here

B4 contains "", not ="".

Upvotes: 1

basic
basic

Reputation: 11968

Try following array formula:

{=SUMPRODUCT((IF(IFERROR(VALUE(B2:B5);FALSE); B2:B5;0)>$A$2:$A$5)*1)}

Array formula after editing is confirmed by pressing ctrl + shift + enter

enter image description here

Upvotes: 2

Variatus
Variatus

Reputation: 14383

Writing "" (nul string) to a cell that is supposed to contain a number (such as a date) is never a good idea because it leads to problems like this one. The solution is in not writing a string (text) to a numeric field. Write zero instead, and then deal with the display of zeros via global settings or cell formatting.

Of course, any date would be greater than zero. If this leads to the wrong count the obvious remedy would be to make a second count of instances where zero is involved and deduct the result of the second count from the first, like, SUMPRODUCT(1)-SUMPRODUCT(2)

Upvotes: 1

Related Questions