SumProduct Formula

Posted on 2011-05-09
Last Modified: 2012-05-11
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.


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


Any suggestions?



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&"")))
    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



    LVL 85

    Expert Comment

    by:Rory Archibald
    Not for points:

    A lot of those quotes appear unnecessary:


    LVL 31

    Expert Comment

    by:Rob Henson
    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:


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

    Rob H

    Author Comment

    Thank you Experts, for the suggestions and tips.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    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. …
    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now