How to retain part of formula's criteria as static
in [Functions]
|
Prev: IF Function
Next: putting a date in an if statement
From: --Viewpoint on 21 May 2010 13:05 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 21 May 2010 13:11 ?? -- 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 21 May 2010 13:16 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 21 May 2010 13:24 --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 21 May 2010 13:46
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.) |