Hi,

I have been creating a workbook in which i download a load of transactions from one system and dump then into another sheet.

I have created a formula that will total the information i want based on 3 criteria.
=sumproduct((Data!F2:F3939)*(Data!C2:C3939=C30)*(Data!D2:D3939=D30)*(Data!E2:E3939=E30))

This works fine and will gather all the information I require.
The problem i have is that this formula is replicated hundreds of times on the sheet and next week the number of transactions may change so the range will not be F2:F3939

I tried using a blanket range of say 10000 transactions F2:F10000, but this returned either a #N/A error or a #Value error. it is only when i use the exact number of filled rows that the formula works.

Should i just autofill the data sheet with 10000 transactions all containing the digit one to try and trick it? or what i am trying to do is use a function CountA in cell b2 of my original sheet

=Counta(Data!A1:A10000)

this will return my number of filled rows so i can make a reference to another cell to make the cell range i.e. ="F" & B2
this should return F3939 as the value. now i need to make the cell ranges in my formula
-sumproduct((Data!F2:"value of B2")

any ideas on how i can go about this so it will automatically count the rows and use that value as the cell range within the formula?

Sorry for the long winded approach but i need to explain fully to hopefully get some help with this,

Thanks for any help.

Regards,

Tubbz