SQL Server COUNT() seems to be ignoring WHERE
You need to add the same where clause in your union select.

SELECT     SiteTour, COUNT(SiteTour) AS count
FROM       tblConference
WHERE      (RegistrationType NOT LIKE 'cancellation')
GROUP BY   SiteTour
UNION ALL
SELECT     'TOTAL' AS SiteTour, COUNT(tblConference_1.SiteTour) AS Expr1
FROM       tblConference AS tblConference_1
WHERE      (tblConference_1.RegistrationType NOT LIKE 'cancellation')