Sum/Count of cells spanning different columns/rows

[Wellie]

Can someone please tell me how can I calculate the
average of the value in cells of column A, D & G for
Group XX, B. E & H for Group YY and C, F & H for Group ZZ
and store the average data in col K, L & M respective as
shown in layout below

PS: In some cases, the cell is empty rather than contains
value 0.

R/C A B C D E F G H I K L M
1 Project A Project B Project C Average
2 XX YY ZZ XX YY ZZ XX YY ZZ XX YY ZZ
3 3 1 2 3 3 Avg-XX Avg-YY Avg-
ZZ

where Row 1 is name of project
Row 2 is the heading
Row 3 is data
Note: The real data is on a structured form.

I thought about using the sumif() function but it sums
disregard what group the values below to.

Your help is appreciate.,

I thought about using @sumif(A3:H3)/((

 

[Frank Kabel]

Hi
maybe something like
=SUMIF(A2:M2,"XX",A3:M3)/COUNTIF(A2:M2,"XX")

 

[Wellie]

Thanks Frank,

I tried this before posting because it only solves half
of the problem. The problem is that the COUNTIF()
function all cells that match "XX" include those cells in
row 3 that are either blank or contains 0s. I want to
exclude empty or 0 cells.

If you have another suggestion will be great.

 

[Peo Sjoblom]

=SUMIF(A2:M2,"XX",A3:M3)/SUMPRODUCT(--(A2:M2="XX"),--(A3:M3<>0))

or

=AVERAGE(IF((A2:M2="XX")*(A3:M3<>0),A3:M3))

entered with ctrl + shift & enter