Formula ranges

[Guest]

Dear Wizards,

I've got lots and lots of rows of data.
I've got a grid of a hundred or so formulas that look similar to the
following:
=SUMPRODUCT((WEEKDAY($A$28:$A$393)=1)*(H$28:H$393))
While the formulas are not all identical, the nice thing is that they all
look at the same NUMERIC component of the range i.e. 28,393.

I'd like to periodically look at different ranges with these formulas. Is
there a way I can just put the range numbers in a couple of cells and have
all of my formulas change the scope of the data they're looking at i.e. 50 in
one cell and 200 in another cell woould change the formulas to look like:
=SUMPRODUCT((WEEKDAY($A$50:$A$200)=1)*(H$50:H$200))

TIA for your help!
Sam

 

[Bernie Deitrick]

Sam,

A formula like this:

=SUMPRODUCT((WEEKDAY(INDIRECT("$A$" & CellWith50 & ":$A$" & CellWith200))=1)*(INDIRECT("$H$" &
CellWith50 & ":$H$" & CellWith200)))

With example cells:

=SUMPRODUCT((WEEKDAY(INDIRECT("$A$"&C1&":$A$"&D1))=1)*(INDIRECT("$H$"&C1&":$H$"&D1)))

HTH,
Bernie
MS Excel MVP

 

[T. Valko]

One way:

A1 = 50
B1 = 200

=SUMPRODUCT(--(WEEKDAY(INDIRECT("A"&A$1&":A"&B$1))=1),INDIRECT("H"&A$1&":H"&B$1))

Note that this makes the range absolute.

Biff