Sumproduct Text and Numbers?

[AYouQueTai]

Hello,

Cant seem to figure this one out. In my report sheet, In cell B4, I am trying to add a Sumproduct formula that would Check the idle time (Minutes) for the Truck number from B1 and the date in B2.
So I figured =SUMPRODUCT((Data[Vehicle]=$B$1)*(Data[Date]=$B$2)*(Data[Minutes])) would work but it gives me a #Value error. I assume it's because I have this formula =IF(RIGHT(LEFT($D2,2),1)="m",LEFT($D2,1),LEFT($D2,2)) in
my minutes section. I read a few posts and thought this would solve the problem =SUMPRODUCT(--(Data[Vehicle]=$B$1)*(Data[Date]=$B$2)*(Data[Minutes])) but it didn't. Then tried =SUMPRODUCT((Data[Vehicle]=$B$1)*(Data[Date]=$B$2)*(Data[Minutes])*1). Can anyone explain what is happening?

Thanks,

(Samsara)_Idling_Report_.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Vehicle	Idle Event Start	Idle Event End	Idle Event Duration	PTO Status	Fuel Used	Address	Date	Minutes	Seconds
2	Truck 019	2024/6/3, 06:56	2024/6/3, 07:02	5m 11s	Inactive	0.35		Jun-03	5	11
Data

Cell Formulas
Range	Formula
H2	H2	=IF(RIGHT(LEFT([@[Idle Event Start]],9),1)=",",DATEVALUE(LEFT([@[Idle Event Start]],8)),IF(RIGHT(LEFT([@[Idle Event Start]],10),1)=",",DATEVALUE(LEFT([@[Idle Event Start]],9)),IF(RIGHT(LEFT([@[Idle Event Start]],11),1)=",",DATEVALUE(LEFT([@[Idle Event Start]],10)),"No")))
I2	I2	=IF(RIGHT(LEFT($D2,2),1)="m",LEFT($D2,1),LEFT($D2,2))
J2	J2	=IF(LEFT(RIGHT($D2,5),1)="m",MID($D2,3,3),MID($D2,4,1))

(Samsara)_Idling_Report_.xlsx
	A	B
1	Truck	Truck 019
2	Date	Jun-03
3
4	Idle Time
5	Fuel Used
Report

Cells with Data Validation
Cell	Allow	Criteria
B1	List	=INDIRECT("Trucks")

 

[Joe4]

This formula:

=IF(RIGHT(LEFT($D2,2),1)="m",LEFT($D2,1),LEFT($D2,2))

while return a Text value, not a numeric one.

If all the characters it returns are numbers, then making this update to it should coerce it to be a number:

=IF(RIGHT(LEFT($D2,2),1)="m",LEFT($D2,1),LEFT($D2,2))+0

If you make that change, does your other formula that references this column work?