Excel formula help!

[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!

[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

[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")*(Data!$D$2:$D$9999=A8)*(Data!$N$2:$N$9999="GOOD")*(Data!$H$2:$H$9999))+SUMPRODUCT((Data!$G$2:$G$9999>=Report!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.