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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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.) |
#2
|
|||
|
|||
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.) |
#3
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|