Sum Product confusion

[bigry26]

Hi Everyone,

I have a formula that I'd like to write but i either get #value ! or 0 for the answer but 0 is not the correct answer.

This data is on a separate spread sheet.

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Date[/TD]
[TD]Shift[/TD]
[TD]Reason[/TD]
[TD]Time[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]08/26/18[/TD]
[TD]2[/TD]
[TD]Washing[/TD]
[TD]1:00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]08/26/18[/TD]
[TD]2[/TD]
[TD]Washing[/TD]
[TD]1:00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]08/26/18[/TD]
[TD]2[/TD]
[TD]Washing[/TD]
[TD]4:00[/TD]
[/TR]
</tbody>[/TABLE]


I need to know how much time was spent washing on 2nd shift on 08/26/18 with sumproduct formula.

Current formula is =SUMPRODUCT(OTHER SPREAD SHEET!$A:$A=$F$1)*(OTHER SPREAD SHEET$B:$B=$I$1)*(OTHER SPREAD SHEET $E:$E=A44)*OTHER SPREAD SHEET!$F:$F

$F$1 = DATE
$I$1 = SHIFT
A44 = REASON
$F:$F = Time down for.

I know i can't use sumif because both spread sheets are rarely open at the same time. Thank you in advance.

 

[Aladin Akyurek]

A shhet name like OTHER SPREAD SHEET must be quoted like this: 'OTHER SPREAD SHEET'.

=SUMIFS(SumRange,DateRange,$F$1,ShiftRange,$I$1,ReasonRange,A44)

=SUMPRODUCT(SumRange,(DateRange=$F$1)*(ShiftRange=$I$1)*(ReasonRange,A44))


Both formula should work as intended. But avoid referencing whole columns for reasons of efficiency.

 

[bigry26]

The reason i was referencing the whole column is the data sheet is growing daily. If i select a range, when data is added would it be included? The reason i can't use sumifs because I'm taking data from three spread sheets to one for a performance report and they won't be open at the same time.

 

[Aladin Akyurek]

The reason i was referencing the whole column is the data sheet is growing daily. If i select a range, when data is added would it be included? The reason i can't use sumifs because I'm taking data from three spread sheets to one for a performance report and they won't be open at the same time.

Does the SUMPRODUCT version work for you? If it does, care to post that formula as you implemented?