So I've got a huge sheet with sales numbers for top 400 clients over last
four years, broken into segments (i.e., PRODUCE, CANNED GOODS, FROZEN GOODS,
BAKERY).

There's a TOTAL column that adds the segments for each year
(2006TOTAL=2006PRODUCE+2006CANNED_GOODS+2006FROZEN _GOODS+2006BAKERY).

Not every client has sales in every year. In those years, the "YEARTOTAL" = 0.

Nonetheless, I want to get an average sale, for customers that have sales.

So:

A 10
B -
C 8
D 3
E -

The average for that year of customers that have sales is 7 - (10+8+3)/3.


If I do "AVERAGE" in a Pivot table, it gives 4.1 - (10+8+3)/5.

I tried filters - less than desirable, but better than nothing - and it
still gives 4.1. Because there's a formula in there, it counts as an entry in
the calc. COUNT and COUNTA (SUBTOTAL(2,[]) and SUBTOTAL (3,[]), respectively)
both give me 5 for the list.

I know I can do a COUNTIF, but how can I build that into filters, etc.? The
list of clients is changing, and I want this to be robust, not flashfrozen.

Thanks!