• Status: Solved
• Priority: Medium
• Security: Public
• Views: 292

# SumProduct Formula

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
lkirke
1 Solution

Commented:
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

Commented:
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

Commented:
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

IT & Database AssistantCommented:
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 Commented:
Thank you Experts, for the suggestions and tips.
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.