How to retain part of formula's criteria as static
 

Below is a formula that I want A50:A100046 to remain static so when I "fill
down" the information doesn't change.

=SUMPRODUCT(--(MONTH(Data!A50:A10046)=7),--(YEAR(Data!A50:A10046)=2008))

Can you suggest how I can write the formula to accomplish my goal? (Just an
FYI: I'm basing the month and year on a fiscal year beginning July 1 through
June 30, so I'm manually changing this info.)

How to retain part of formula's criteria as static
 

??

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"--Viewpoint" wrote in message
...
Below is a formula that I want A50:A100046 to remain static so when I
"fill
down" the information doesn't change.

=SUMPRODUCT(--(MONTH(Data!A50:A10046)=7),--(YEAR(Data!A50:A10046)=2008))

Can you suggest how I can write the formula to accomplish my goal? (Just
an
FYI: I'm basing the month and year on a fiscal year beginning July 1
through
June 30, so I'm manually changing this info.)

How to retain part of formula's criteria as static
 

You would insert the "$" before things you don't want to change.

=SUMPRODUCT(--(MONTH(Data!A$50:A$10046)=7),--(YEAR(Data!A$50:A$10046)=2008))

Or perhaps, the more flexible formula:
=SUMPRODUCT(--(TEXT(Data!A$50:A$10046,"mmyyyy")="072008"))
This way you have fewer calculations, and formula won't crash if a
non-numerical value is in the range A50:A10046.
--
Best Regards,

Luke M
"--Viewpoint" wrote in message
...
Below is a formula that I want A50:A100046 to remain static so when I
"fill
down" the information doesn't change.

=SUMPRODUCT(--(MONTH(Data!A50:A10046)=7),--(YEAR(Data!A50:A10046)=2008))

Can you suggest how I can write the formula to accomplish my goal? (Just
an
FYI: I'm basing the month and year on a fiscal year beginning July 1
through
June 30, so I'm manually changing this info.)

How to retain part of formula's criteria as static
 

--Viewpoint,

Learn everything you can on absolute references vs relative references.

Add dollar signs ($) to the rows/columns you want to remain static:

=SUMPRODUCT(--(MONTH(Data!A50:A10046)=7),--(YEAR(Data!A50:A10046)=2008))

would become

=SUMPRODUCT(--(MONTH(Data!$A$50:$A$10046)=7),--(YEAR(Data!A50:A10046)=2008))

HTH,

Conan Kelly


"--Viewpoint" wrote in message
...
Below is a formula that I want A50:A100046 to remain static so when I
"fill
down" the information doesn't change.

=SUMPRODUCT(--(MONTH(Data!A50:A10046)=7),--(YEAR(Data!A50:A10046)=2008))

Can you suggest how I can write the formula to accomplish my goal? (Just
an
FYI: I'm basing the month and year on a fiscal year beginning July 1
through
June 30, so I'm manually changing this info.)

How to retain part of formula's criteria as static
 

Put dollar signs in front of the row and/or column references to make them
absolute (static): $A$50:$A$100046

Hope this helps,

Hutch

"--Viewpoint" wrote:

Below is a formula that I want A50:A100046 to remain static so when I "fill
down" the information doesn't change.

=SUMPRODUCT(--(MONTH(Data!A50:A10046)=7),--(YEAR(Data!A50:A10046)=2008))

Can you suggest how I can write the formula to accomplish my goal? (Just an
FYI: I'm basing the month and year on a fiscal year beginning July 1 through
June 30, so I'm manually changing this info.)

How to retain part of formula's criteria as static
 

Use absolute referencing. Below are the different reference styles.
A1 Relative referencing. Both column and row will change if you copy or drag
the formula.
$A1 The column reference is fixed and will not change
A$1 The row reference is fixed and will not change.
$A$1 Column and row reference are fixed.

--
Jacob (MVP - Excel)


"--Viewpoint" wrote:

Below is a formula that I want A50:A100046 to remain static so when I "fill
down" the information doesn't change.

=SUMPRODUCT(--(MONTH(Data!A50:A10046)=7),--(YEAR(Data!A50:A10046)=2008))

Can you suggest how I can write the formula to accomplish my goal? (Just an
FYI: I'm basing the month and year on a fiscal year beginning July 1 through
June 30, so I'm manually changing this info.)

How to retain part of formula's criteria as static
 

I did try inserting $ but it is not accepted in this formula so I thought
there might be another way.

"Luke M" wrote:

You would insert the "$" before things you don't want to change.

=SUMPRODUCT(--(MONTH(Data!A$50:A$10046)=7),--(YEAR(Data!A$50:A$10046)=2008))

Or perhaps, the more flexible formula:
=SUMPRODUCT(--(TEXT(Data!A$50:A$10046,"mmyyyy")="072008"))
This way you have fewer calculations, and formula won't crash if a
non-numerical value is in the range A50:A10046.
--
Best Regards,

Luke M
"--Viewpoint" wrote in message
...
Below is a formula that I want A50:A100046 to remain static so when I
"fill
down" the information doesn't change.

=SUMPRODUCT(--(MONTH(Data!A50:A10046)=7),--(YEAR(Data!A50:A10046)=2008))

Can you suggest how I can write the formula to accomplish my goal? (Just
an
FYI: I'm basing the month and year on a fiscal year beginning July 1
through
June 30, so I'm manually changing this info.)


.

How to retain part of formula's criteria as static
 

THANK YOU!

"Jacob Skaria" wrote:

Use absolute referencing. Below are the different reference styles.
A1 Relative referencing. Both column and row will change if you copy or drag
the formula.
$A1 The column reference is fixed and will not change
A$1 The row reference is fixed and will not change.
$A$1 Column and row reference are fixed.

--
Jacob (MVP - Excel)


"--Viewpoint" wrote:

Below is a formula that I want A50:A100046 to remain static so when I "fill
down" the information doesn't change.

=SUMPRODUCT(--(MONTH(Data!A50:A10046)=7),--(YEAR(Data!A50:A10046)=2008))

Can you suggest how I can write the formula to accomplish my goal? (Just an
FYI: I'm basing the month and year on a fiscal year beginning July 1 through
June 30, so I'm manually changing this info.)