Agent Plissken

Hi guys.

I need to change the range referenced in a formula depending on the value in another cell.

To simplify: =Sum(A1:A99) but where it says A99, I need that to be based on the value of cell B1.

So if Cell B1 = "A99"

Then it would be Sum of A1 - A99

If cell B1 = "A9999"
Then it would be sum of A1 - A9999

Any help would be appreciated!

__________________
Dyin' ain't much of a living, boy

InfiniteNothing

=SUMIF(B1:B18,"<"&D1,C1:C18)
Where B1:B18 is a list of a 1,2,3...
D1 is 99 or 9999 in your example
and C1:C18 is a list of all possible cells you might want to sum

__________________
As for our common defense, we reject as false the choice between our safety and our ideals.

Agent Plissken

Thanks for your reply...

Let me get more specific, because what you say works, but not in my situation...

I am actually using SUMPRODUCT function, which means I have to specify the exact range of data (i can't simply specify the entire column or it will return an error)

So my formula is as follows:

=SUMPRODUCT(($A$2:$A$1124>=K6)*($A$2:$A$1124<=L6)*($A$2:$A$1124="A")*($D$2:$D$1124=A8)*($N$2:$N$1124 ="GOOD")*($H$2:$H$1124))

So everywhere there is a range "$A$1124" i want to refrence that value in a cell... because depeding on the data set it may be $A$9999, or $A$300, etc.

If it were just one formula i could just update it manually, but there are lots of them.

InfiniteNothing

I'm not sure I get your formula. Sumproduct seems to want to take a list of arrays for me and you seem to have fed it one expression in which you're trying to AND a bunch of boolean expressions.

Maybe this will answer your question: I don't think you can dynamically specify ranges. You have to be clever and sneaky about this sort of thing like creating a derived column where you replace the values you don't want to sum with 0.

Agent Plissken

Well, I took your advice and got sneaky...
I just filled in 'dummy' data that is outside of the date range that I will every be querying for… I put in 9999 rows of dummy data in and set my formula to always check all the way from $A1:$A9999 …. Works like a charm too!

Agent Plissken

Well, apparently im the dummy. I set the formulas as A9999 and it works, even if the cells are blank. Which means I can put in a data set of any size smaller than that and it will work

Finished!
=(L8*8*M8)-SUMPRODUCT((Data!$G$2:$G$9999>=Report!K6)*(Data!$G$2:$G$9999<=Report!L6)*(Data!$A$2:$A$9999="C")*(Da ta!$D$2:$D$9999=A8)*(Data!$N$2:$N$9999="GOOD")*(Data!$H$2:$H$9999))+SUMPRODUCT((Data!$G$2:$G$9999>=R eport!K6)*(Data!$G$2:$G$9999<=Report!L6)*(Data!$A$2:$A$9999="B")*(Data!$D$2:$D$9999=A8)*(Data!$N$2:$ N$9999="GOOD")*(Data!$H$2:$H$9999))

InfiniteNothing

Glad I could help

mcs328

I think I blew a fuse in my brain.

__________________