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.

	A	B	C	D
1	Date	Shift	Reason	Time
2	08/26/18	2	Washing	1:00
3	08/26/18	2	Washing	1:00
4	08/26/18	2	Washing	4:00

<tbody>
</tbody>

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?