Calculating averages for a graph - just like a pivot

[Rick_Terminal]

Hi everyone,

I've got a problem where my solution just isn't good enough and I'm hoping someone will have a better idea.

The problem:
I have to report vehicle utilisation by month, by region, and by "category" (front line, second line, specialist, and average).

I need a line/bar graph that can duplicate the figures produced by the pivot table that is linked directly to the raw data. The issue here is that when there is not a utilisation figure for a particular month/type/region, graph data table will mark this as a 'false zero', where as a pivot table will know not to include it in the average formula - so getting a different result to a straight average() sum.

Unfortunately Pivot Charts are possibly not an option because the graph is on a dynamic dashboard that allows the selection "Region" on the sheet with all the other non-pivot charts - using text boxes and drop down lists/macros.

So far, the only way I can match the results using formulas by using sumif()\countif(), but it's a monster formula to do for all regions, requiring 25xsumif/countif's to return a single type for all regions for a single month!

I hope I'm missing an easy trick here.