PDA

View Full Version : Excel formula help!

Agent Plissken
12-11-2008, 03:38 AM
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
12-11-2008, 09:51 AM
=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
12-11-2008, 06:06 PM

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
12-11-2008, 07:31 PM
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
12-11-2008, 08:35 PM
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
12-11-2008, 08:48 PM
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
12-11-2008, 11:39 PM