Solved

# SumProduct Formula

Posted on 2011-05-09
276 Views
Hello Experts,

Have the following formula, but wish to tweak it slightly by adding an OR statement.

Currently \$D\$3 is a date and \$A\$15 is a time field. Was seeking to add the OR statement to the date reference so that the formula could look at more than one date, either D3 or E3 or BOTH.

=SUMPRODUCT((INDIRECT("'"&B\$7&"'!"&""&B\$8&""):INDIRECT("'"&B\$7&"'!"&""&C\$8&"")=\$D\$3)*(INDIRECT("'"&B\$7&"'!"&""&B\$9&""):INDIRECT("'"&B\$7&"'!"&""&C\$9&"")=\$A15)*(INDIRECT("'"&B\$7&"'!"&""&B\$10&""):INDIRECT("'"&B\$7&"'!"&""&C\$10&"")))

However, when I tweak the formula to the following, I get nothing returned.

=SUMPRODUCT((INDIRECT("'"&B\$7&"'!"&""&B\$8&""):INDIRECT("'"&B\$7&"'!"&""&C\$8&"")=OR(\$D\$3,\$E\$3))*(INDIRECT("'"&B\$7&"'!"&""&B\$9&""):INDIRECT("'"&B\$7&"'!"&""&C\$9&"")=\$A15)*(INDIRECT("'"&B\$7&"'!"&""&B\$10&""):INDIRECT("'"&B\$7&"'!"&""&C\$10&"")))

Any suggestions?

Regards

LK

0
Question by:lkirke

LVL 41

Expert Comment

the function OR (D3, E3) will return TRUE or FALSE, but not an array result set from the comparison you've put together...

One possible solution could be the sum of the two options:

=SUMPRODUCT((INDIRECT("'"&B\$7&"'!"&""&B\$8&""):INDIRECT("'"&B\$7&"'!"&""&C\$8&"")=\$D\$3)*(INDIRECT("'"&B\$7&"'!"&""&B\$9&""):INDIRECT("'"&B\$7&"'!"&""&C\$9&"")=\$A15)*(INDIRECT("'"&B\$7&"'!"&""&B\$10&""):INDIRECT("'"&B\$7&"'!"&""&C\$10&""))) +  SUMPRODUCT((INDIRECT("'"&B\$7&"'!"&""&B\$8&""):INDIRECT("'"&B\$7&"'!"&""&C\$8&"")=\$E\$3)*(INDIRECT("'"&B\$7&"'!"&""&B\$9&""):INDIRECT("'"&B\$7&"'!"&""&C\$9&"")=\$A15)*(INDIRECT("'"&B\$7&"'!"&""&B\$10&""):INDIRECT("'"&B\$7&"'!"&""&C\$10&"")))
0

LVL 41

Accepted Solution

Alternatively - and much simpler, use the + or condition in this fashion:

Try this more simplified approach - note the ( (condition=\$D\$3) + (condition = \$E\$3) ) embedded in the sumproduct

=SUMPRODUCT(((INDIRECT("'"&B\$7&"'!"&""&B\$8&""):INDIRECT("'"&B\$7&"'!"&""&C\$8&"")=\$D\$3)+(INDIRECT("'"&B\$7&"'!"&""&B\$8&""):INDIRECT("'"&B\$7&"'!"&""&C\$8&"")=\$E\$3))*(INDIRECT("'"&B\$7&"'!"&""&B\$9&""):INDIRECT("'"&B\$7&"'!"&""&C\$9&"")=\$A15)*(INDIRECT("'"&B\$7&"'!"&""&B\$10&""):INDIRECT("'"&B\$7&"'!"&""&C\$10&"")))

Enjoy!

Dave
0

LVL 85

Expert Comment

Not for points:

A lot of those quotes appear unnecessary:

=SUMPRODUCT(((INDIRECT("'"&B\$7&"'!"&B\$8):INDIRECT("'"&B\$7&"'!"&C\$8&)=\$D\$3)+(INDIRECT("'"&B\$7&"'!"&B\$8):INDIRECT("'"&B\$7&"'!"&C\$8)=\$E\$3))*(INDIRECT("'"&B\$7&"'!"&B\$9):INDIRECT("'"&B\$7&"'!"&C\$9)=\$A15)*(INDIRECT("'"&B\$7&"'!"&B\$10):INDIRECT("'"&B\$7&"'!"&C\$10)))

0

LVL 31

Expert Comment

What criteria are you using to determine which Date to use?

You could replace the section referring to the date with an IF statement with the cell reference as the result eg:

=IF(OR(\$D\$3="",\$D\$3=0),\$E\$3,\$D\$3)

This checks if D3 is blank or zero and if so uses E3 otherwise uses D3.

Cheers
Rob H
0

Author Comment

Thank you Experts, for the suggestions and tips.
0

## Featured Post

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …