The best way of looking at this sort to problem is to figure out how to do it in intermediate steps. Nothing stops you from creating intermediate "work" tables to put the data into a more useable form. You could create an intermediate table that is used simply to group together the data into a more useable form. You could use vlookup to populate the table based on the criteria in column C. The trick with using Excel is to realize that you do not have to create the perfect in-cell formula in one shot. I think that when an in-cell formula becomes too complex and you are unable to evaluate the intermediate results then you are looking for trouble.

You can do it in various ways.

option 1
Drop the subtotals.
Grab the data and sort it by Column C
Auto subtotals for every change in column C.

option 2
Drop the subtotals.
Create a new set of rows below your data and call the A1 to A8
Now create a formula in each field using =sumif(C1..C10,"A1",{whatever column you are trying to sum}
sumif is very clever because it looks a criteria to decide whether to sum the value sumif (criteria range, criteria, sum range)

option 3
You could write a VB script to run through the data but i think it would be an overkill.

Try these methods, if you don't come right let me know thn I'll do it for you.