Keeping # of Cells constant in a formula

[jeannie v]

Hi Experts:

This is my formula:

=SUMPRODUCT(--('REPORT'!H2:H12000="Dummy"),--('REPORT'!I2:I12000>3))

I want this formula to add the data for "Dummy" that changes every day from
H2 to H12000 on the "REPORT" worksheet and plug the # if it is greater than 3
on another worksheet in the same Workbook.....How can I make the H2:H12000
and the I2:I12000 remain the same every time I pull this data so I don't have
to change the formula coordinates every day? I've tried the $ many different
ways, but it's not working...I've viewed Help, but I'm still not having any
luck. I know it should be simple, but I need help.

Any help you can provide would be appreciated. Thank you.

 

[Gord Dibben]

How can I make the H2:H12000

and the I2:I12000 remain the same every time

=SUMPRODUCT(--(REPORT!$H$2:$H$12000="Dummy"),--(REPORT!$I$2:$I$12000>3))


Gord Dibben MS Excel MVP

 

[T. Valko]

Sounds like you want to use dynamic ranges:

http://contextures.com/xlNames01.html#Dynamic

 

[jeannie v]

Hi Gord: Thank you so much! I appreciate it!

 

[jeannie v]

Hi Biff: I will definitely look into dynamics as soon as possible. Thank
you for the info.

 

[T. Valko]

I may have misunderstood what you were wanting to do!

 

[Gord Dibben]

After re-reading, not so sure if that's what you were asking for.

Might be Biff is more on track but.............?


Gord

 

[Ragdyer]

OR ... maybe trying to work with inserting/deleting rows:

=SUMPRODUCT(--(INDIRECT("$H$2:$H$12000")="Dummy"),--(INDIRECT("$I$2:$I$12000
")>3))

 

[Ragdyer]

Of course, we don't need the absolutes:

=SUMPRODUCT(--(INDIRECT("H2:H12000")="Dummy"),--(INDIRECT("I2:I12000")>3))

 

[jeannie v]

Hi Gord:

Your solution worked perfectly and I used it for 2 applications...Thank you
so much for your expertise.

jeannie v