Sorry Martin. I found the problem- it was with the database itself. Because I
was only testing the formula, I did not complete all columns in the data set,
so it therefore didn't see it as a continuation of the data. When I completed
all columns and therefore had completed data, I found the Subtotal formula
worked. Sorry to have wasted your time. THanks for your help. I'll try to
hide my blonde a bit better next time.

Regards from Australia

Gai


"Martin Fishlock" wrote:

In the last formula what are you trying to calculate.

May an example would help.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Gai" wrote:

I have just worked out that a formula I have been using is not working as I
thought. I am trying to 1) count a number of occurances of a certain risk
appearing, and 2) average them. I filter the data on Sheet1 and Sheet2 is a
reporting page where I do the calculations. I have tried:

Subtotal(1,$AA$5:$AA$992) -for average and subtotal(3,$AA$5:$AA$992) for
count.
AND
SUMPRODUCT(SUBTOTAL(1,OFFSET(Sheet1!$AA$5:$AA$992, ROW(Sheet1!$AA$5:$AA$992)-MIN(ROW(Sheet1!$AA$5:$AA$992)),,1)),--(???)

The question mark is where I come into diffculty. What do I say it equals? I
filter on column A which is a concatenate formula based on month and year.
Each time the report is retrieved it is for different months/years. Because I
made it for others to use, I can't just alter the formula each time.

Could someone help - quickly!!
Thanks