A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How to retain part of formula's criteria as static



 
 
Thread Tools Display Modes
  #1  
Old May 21st, 2010, 06:05 PM posted to microsoft.public.excel.worksheet.functions
--Viewpoint
external usenet poster
 
Posts: 23
Default 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.)
  #3  
Old May 21st, 2010, 06:16 PM posted to microsoft.public.excel.worksheet.functions
Luke M[_4_]
external usenet poster
 
Posts: 451
Default 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.)



  #4  
Old May 21st, 2010, 06:24 PM posted to microsoft.public.excel.worksheet.functions
Conan Kelly
external usenet poster
 
Posts: 429
Default 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.)



  #5  
Old May 21st, 2010, 06:46 PM posted to microsoft.public.excel.worksheet.functions
Tom Hutchins
external usenet poster
 
Posts: 722
Default 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.)

  #6  
Old May 21st, 2010, 06:46 PM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default 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.)

  #7  
Old May 21st, 2010, 07:02 PM posted to microsoft.public.excel.worksheet.functions
--Viewpoint
external usenet poster
 
Posts: 23
Default 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.)



.

  #8  
Old May 21st, 2010, 07:53 PM posted to microsoft.public.excel.worksheet.functions
--Viewpoint
external usenet poster
 
Posts: 23
Default 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.)

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:51 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.