Hi

Posting DDL and your sample data as insert statements will make it easier
for people to answer your post

USE TEMPDB
GO
CREATE TABLE AREACounts ( name char(5), subarea char(8), member char(7) )
GO

INSERT INTO AREACounts ( name, subarea, member )
SELECT 'AreaA','SubAreaA','Member1'
UNION ALL SELECT  'AreaA','SubAreaA','Member2'
UNION ALL SELECT  'AreaA','SubAreaA','Member3'
UNION ALL SELECT  'AreaA','SubAreaA','Member1'
UNION ALL SELECT  'AreaA','SubAreaA','Member1'
UNION ALL SELECT  'AreaA','SubAreaA','Member2'
UNION ALL SELECT  'AreaA','SubAreaA','Member1'
UNION ALL SELECT  'AreaA','SubAreaA','Member3'
UNION ALL SELECT  'AreaA','SubAreaA','Member1'
UNION ALL SELECT  'AreaA','SubAreaA','Member1'
UNION ALL SELECT  'AreaA','SubAreaA','Member2'
UNION ALL SELECT  'AreaA','SubAreaA','Member3'
UNION ALL SELECT  'AreaA','SubAreaA','Member1'
UNION ALL SELECT  'AreaA','SubAreaA','Member1'
UNION ALL SELECT  'AreaA','SubAreaA','Member2'
UNION ALL SELECT  'AreaA','SubAreaA','Member1'
UNION ALL SELECT  'AreaA','SubAreaB','Member3'
UNION ALL SELECT  'AreaA','SubAreaB','Member1'
UNION ALL SELECT  'AreaA','SubAreaB','Member1'
UNION ALL SELECT  'AreaA','SubAreaB','Member2'
UNION ALL SELECT  'AreaA','SubAreaB','Member3'
UNION ALL SELECT  'AreaA','SubAreaB','Member1'
UNION ALL SELECT  'AreaB','SubAreaA','Member1'
UNION ALL SELECT  'AreaB','SubAreaB','Member2'
UNION ALL SELECT  'AreaB','SubAreaA','Member1'
UNION ALL SELECT  'AreaB','SubAreaB','Member3'
UNION ALL SELECT  'AreaB','SubAreaA','Member1'

SELECT [name] AS Members_Per_SubArea,
[SubAreaA], [SubAreaB], [SubAreaA] + [SubAreaB] as [total]
FROM
(SELECT DISTINCT [name], SubArea, member
   FROM AREACounts
) AS SourceTable
PIVOT
(
COUNT([Member])
FOR SubArea IN ([SubAreaA], [SubAreaB])
) AS PivotTable

To get the total you can either union with the above query as a derived
table although this may be better as a view

CREATE VIEW vw_pivot AS
SELECT [name],
[SubAreaA], [SubAreaB], [SubAreaA] + [SubAreaB] as [total]
FROM
(SELECT DISTINCT [name], SubArea, member
   FROM AREACounts
) AS SourceTable
PIVOT
(
COUNT([Member])
FOR SubArea IN ([SubAreaA], [SubAreaB])
) AS PivotTable

This gives:
SELECT [name],
[SubAreaA], [SubAreaB], [total]
FROM vw_pivot
UNION ALL
SELECT 'Total',
 SUM([SubAreaA]),
 SUM([SubAreaB]),
 SUM([total])
FROM vw_pivot

This would not guarantee order  but you can add an order column and make
this a derived table

SELECT [name], [SubAreaA], [SubAreaB], [total]
FROM (
   SELECT 1 AS srtcol, [name],
   [SubAreaA], [SubAreaB], [total]
   FROM vw_pivot
   UNION ALL
   SELECT 2, 'Total',
     SUM([SubAreaA]),
     SUM([SubAreaB]),
     SUM([total])
   FROM vw_pivot
) A
ORDER BY srtcol ASC

John