How can I use Count() or Sum() with advanced criteria such as matching the most recent rows where a condition is true?
Using a common table expression for Max(RecordDate) might help.

Not sure if this code is just what you need, but you can modify. The script works on the sample table that I created from your question.

DECLARE @StartDate datetime = '2019-05-10 08:00:00';
DECLARE @EndDate datetime = '2019-05-24 09:00:00';
DECLARE @StageID int = 6;

WITH MyRecentSerialNumber AS
    (SELECT ID, MAX(RecordDate) AS MaxRecordDate,   SerialNumber,   ProductID,  StageID,        Result 
    FROM t1
    WHERE Result = 1
    GROUP BY ID, SerialNumber, ProductID, StageID, Result)
SELECT ProductID, COUNT (*) 'Total',
    SUM (CASE WHEN Result = 1 THEN 1 ELSE 0 END) 'Output'
FROM MyRecentSerialNumber
WHERE MaxRecordDate BETWEEN @StartDate AND @EndDate AND StageID = @StageID
GROUP BY ProductID;


Output is the same with the sample data, but you can test and adjust with data that should be excluded.