I am trying to find a value that is in column L of another sheet (This workbook may be closed hence Sumproduct over Sumifs) but for some reason the Sumifs works but Sumproduct returns #N/A?
Input values and destination values:
Active Sheet
- B2062 = BW3089657
- G2062 = 43303.2336574074 (22/07/2018 01:18:09)
- H2062 = 43303.5145833333 (22/07/2018 12:21:00)
wb.xlsb
- D = BW3089657
- O = 43300 (19/07/2018)
- P = 43304 (23/07/2018)
Formulas and return values.
=SUMPRODUCT(--('[wb.xlsb]Sheet1'!$D:$D=B2062)*(G2062>='[wb.xlsb]Sheet1'!$O:$O)*(H2062<='[wb.xlsb]Sheet1'!$P:$P),('[wb.xlsb]Sheet1'!$L:$L))
Returns = #N/A
=SUMIFS('[wb.xlsb]Sheet1'!$L:$L,'[wb.xlsb]Sheet1'!$D:$D,B2062,'[wb.xlsb]Sheet1'!$O:$O,"<="&G2062,'[wb.xlsb]Sheet1'!$P:$P,">="&H2062)
Returns = 29.04%
I hope I am not making a silly mistake here but given the sumifs formula works i am guessing its an issue with either sumproduct limitations, formatting or my understanding of this formula.