Refer the below test query for your reference.

SQL

DECLARE @tblFlats AS TABLE(Id INT,House VARCHAr(100),FlatNumber VARCHAR(100),[sign] INT)
     
INSERT INTO @tblFlats VALUES
(1,'1','1,2'  ,1),
(2,'1','39,40',1),
(3,'5',''     ,1),
(4,'6',''   ,1),
(5,'7','5'    ,1),
(6,'7','6'    ,1),
(7,'8','10,11',1),
(8,'8','9'    ,1),
(9,'9,10',''  ,1),
(10,'1','1,2'  ,1),
(11,'9,10',''  ,1),
(12,'5',''  ,1)

;WITH cte 
AS
(
  SELECT DISTINCT House, FlatNumber FROM @tblFlats
)


 
SELECT SUM (CASE WHEN (LTRIM(RTRIM(ISNULL(House,''))) <> '' AND LTRIM(RTRIM(ISNULL(FlatNumber,''))) <> '')
                 THEN (LEN(FlatNumber) - LEN(REPLACE(FlatNumber,',', ''))+1)
                 WHEN (LTRIM(RTRIM(ISNULL(House,''))) <> '')
                 THEN (LEN(House) - LEN(REPLACE(House,',', ''))+1)
                 WHEN (LTRIM(RTRIM(ISNULL(FlatNumber,''))) <> '')
                 THEN (LEN(FlatNumber) - LEN(REPLACE(FlatNumber,',', ''))+1)
                 END) As Result
FROM cte

Output