Summing Values accross multiple sheets

Three dimensional SUMIF/COUNTIF is not trivial (pending requirements & layout), see the mcgimpsey overview of the various options:

http://www.mcgimpsey.com/excel/threedsumif.html

Note: should you opt for THREED / morefunc.xll ... the morefunc add-in can be "embedded" into a file and distributed (akin to an old toolbar) therefore it does not need to be installed individually on client machines.

Generally speaking creating an identical table on each individual sheet that generates the requisite data for the summary is best - this way you can just sum common cells using a =SUM(Sheet1:Sheet10!A1) approach... far and away the simplest method available.

Using the SUMPRODUCT / INDIRECT method will not be great performance wise given the SUMPRODUCT will be Volatile (given use of INDIRECT) so pending size of model this could lead to serious performance issues.

Re: VBA - yes you could use a UDF but UDF's are generally slower than native formulae ... I would try the non-VBA methods first ... if these prove too complex or cumbersome then perhaps consider a UDF (but if going down the VBA route I'd just opt for Morefunc.xll)