From: --Viewpoint on
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.)
From: Don Guillett on
??

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"--Viewpoint" <Viewpoint(a)discussions.microsoft.com> wrote in message
news:994BFAFC-9BDB-44DF-871C-DC384265C671(a)microsoft.com...
> 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.)

From: Luke M on
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" <Viewpoint(a)discussions.microsoft.com> wrote in message
news:994BFAFC-9BDB-44DF-871C-DC384265C671(a)microsoft.com...
> 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.)


From: Conan Kelly on
--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" <Viewpoint(a)discussions.microsoft.com> wrote in message
news:994BFAFC-9BDB-44DF-871C-DC384265C671(a)microsoft.com...
> 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.)


From: Tom Hutchins on
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.)
 |  Next  |  Last
Pages: 1 2
Prev: IF Function
Next: putting a date in an if statement