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

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
Asked:
lkirke
1 Solution
 
dlmilleCommented:
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
 
dlmilleCommented:
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
 
Rory ArchibaldCommented:
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
 
Rob HensonIT & 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
 
lkirkeAuthor Commented:
Thank you Experts, for the suggestions and tips.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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