Pasting a formula in multiple cells without changing the range

[Jeff Wheeler]

I am trying to paste a formula in multiple cells and I want the range to stay
the same throughout the sheet. When pasting it obviously changes the range
to the next cell in sequence. I'm using the following formula
=SUMPRODUCT(--(A2:A4000=70),--(B2:B4000="Product Type"),C2:C4000) and want to
kep the range A2:A4000, same for column B and C.

[CLR]

=SUMPRODUCT(--($A$2:$A$4000=70),--($B$2:$B$4000="Product Type"),$C$2:$C$4000)


The Dollar signs convert from Relative references to Absolute references.

hth
Vaya con Dios,
Chuck, CABGx3



"Jeff Wheeler" wrote:

> I am trying to paste a formula in multiple cells and I want the range to stay
> the same throughout the sheet. When pasting it obviously changes the range
> to the next cell in sequence. I'm using the following formula
> =SUMPRODUCT(--(A2:A4000=70),--(B2:B4000="Product Type"),C2:C4000) and want to
> kep the range A2:A4000, same for column B and C.

[CWillis]

=SUMPRODUCT(--($A$2:$A$4000=70),--($B$2:$B$4000="Product Type"),$C$2:$C$4000)

"Jeff Wheeler" wrote:

> I am trying to paste a formula in multiple cells and I want the range to stay
> the same throughout the sheet. When pasting it obviously changes the range
> to the next cell in sequence. I'm using the following formula
> =SUMPRODUCT(--(A2:A4000=70),--(B2:B4000="Product Type"),C2:C4000) and want to
> kep the range A2:A4000, same for column B and C.

[Bondi]

Jeff Wheeler wrote:
> I am trying to paste a formula in multiple cells and I want the range to stay
> the same throughout the sheet. When pasting it obviously changes the range
> to the next cell in sequence. I'm using the following formula
> =SUMPRODUCT(--(A2:A4000=70),--(B2:B4000="Product Type"),C2:C4000) and want to
> kep the range A2:A4000, same for column B and C.

Hi,

Maybe you can lock your cells,

=SUMPRODUCT(--($A$2:$A$4000=70),--($B$2:$B$4000="Product
Type"),$C$2:$C$4000)

See also

The difference between relative and absolute references in the Help

Regards,
Bondi