Using a OR function with a SUMPRUDUCT 1

[jupops]

Good Evening All, can you help

I am trying to write a sumproduct formula that will print out a result if criteria is met.

.I have three worksheets that are named Sunday, Monday and Data.

The Sunday worksheets looks like

0 SUNDAY 11
0 SUNDAY 12
38992 SUNDAY 13
0 SUNDAY 14
0 SUNDAY 15
0 SUNDAY 16
768 SUNDAY 17
661 SUNDAY 18
539 SUNDAY 19
412 SUNDAY 1110
283 SUNDAY 111
70 SUNDAY 12
899 SUNDAY 113
915 SUNDAY 114
.
The Monday worksheet looks like

0 MONDAY 1
0 MONDAY 2
38992 MONDAY 3
0 MONDAY 4
0 MONDAY 5
0 MONDAY 6
768 MONDAY 7
661 MONDAY 8
539 MONDAY 9
412 MONDAY 10
283 MONDAY 11
899 MONDAY 12
915 MONDAY 13
931 MONDAY 14

And the data work sheet looks

Store Number Store Details Day
412 MCR S/S LEVEL T Sunday

When I use the following formulas I get the result I need


=SUMPRODUCT((Sunday!A4:A15=Data!B5)*(Sunday!B4:B15=Data!D5)*Sunday!C4:C15)


=SUMPRODUCT((Monday!A4:A15=Data!B5)*(Monday!B4:B15=Data!D5)*Monday!C4:C15)

But I am trying to merge the two formula with a OR function, so depending if it is a Sunday or a Monday it will show a different result.

I have tried different combinations with no luck (for example - =SUMPRODUCT(Sunday!A4:A15=Data!B5)*(Sunday!B4:B15=Data!D5)*(Sunday!C4:C15)*(SUMPRODUCT(Monday!A4:A15=Data!B5)*(Monday!B4:B15=Data!D5)*Monday!C4:C15) and =SUMPRODUCT(Sunday!A4:A15=Data!B5)*(Sunday!B4:B15=Data!D5)*(Sunday!C4:C15)*OR(SUMPRODUCT(Monday!A4:A15=Data!B5)*(Monday!B4:B15=Data!D5)*Monday!C4:C15))

Can you help???

Regards

Jupops

 

[KenWright]

Does this not work??

=SUMPRODUCT((Sunday!A4:A15=Data!B5)*(Sunday!B4:B15=Data!D5)*Sunday!C4:C15)+SUMPRODUCT((Monday!A4:A15=Data!B5)*(Monday!B4:B15=Data!D5)*Monday!C4:C15)

Regards
Ken.......

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[jupops]

Thank you,you are a star, I can't tell how long I have been messing around with the formula,

Cheers

Julia

 

[KenWright]

LOl - no problem, but all I did was add your two existing formulas together, because only one of them will ever return you a result, so addition works in this case.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------