use a cell value as another a cell reference in another formula

[TubbzUK]

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

[zbor]

Try this:

=sumproduct((Data!F2:F10000), --(Data!C2:C10000=C30), --(Data!D2:D1000=D30), --(Data!E2:E10000=E30))

[TubbzUK]

Hi,

will try this later when i get back from work,
just a quick question, what do the -- mean before the brackets?

regards,

Tubbz

[jason.b75]

Try

=SUMIFS(Data!F:F,Data!C:C,C30,Data!D:D,D30,Data!E:E,E30)

With the sumproduct formula the Data sheet ranges should be absolute, not relative.

[TubbzUK]

Both of the solutions provided worked great, I will most likely go with the SUMIFS function as this way i never have to define a range and just choose the columns required. Then it makes no odds how much data is exported in.

Thanks for the help guys, much appreciated,

Regards,

TubbzUK

[Cutter]

@ TubbzUK

Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.