Prev: IF Function
Next: putting a date in an if statement
From: Jacob Skaria on 21 May 2010 13:46
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.)
From: --Viewpoint on 21 May 2010 14:02
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" <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: --Viewpoint on 21 May 2010 14:53
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.)
First  |  Prev  | 
Pages: 1 2
Prev: IF Function
Next: putting a date in an if statement