| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Question
I have a summary worksheet that totals up some values on one of my other
worksheets. The problem is that I have to keep changing the range of my formula because the data on my other worksheet changes daily and more rows are always added. How can I set up my formula to automatically go to the last row it finds data in on my other worksheet? Here's an example of my formula: =SUMPRODUCT(--(MONTH('PC-01'!$O$2:$O$20000)=MONTH($A11)),--(YEAR('PC-01'!$O$2:$O$20000)=YEAR($A11)),'PC-01'!$AB$2:$AB$20000) As you can see it's set up from O2 to O20000. I keep having to change the O20000 since new data is always added. Is there an easy way to automate this? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Question
You can assign names to your ranges.
Here's some info from Debra Dalgleish's web site on using dynamic ranges: http://www.contextures.com/xlNames01.html#Dynamic -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Secret Squirrel" wrote in message ... I have a summary worksheet that totals up some values on one of my other worksheets. The problem is that I have to keep changing the range of my formula because the data on my other worksheet changes daily and more rows are always added. How can I set up my formula to automatically go to the last row it finds data in on my other worksheet? Here's an example of my formula: =SUMPRODUCT(--(MONTH('PC-01'!$O$2:$O$20000)=MONTH($A11)),--(YEAR('PC-01'!$O$ 2:$O$20000)=YEAR($A11)),'PC-01'!$AB$2:$AB$20000) As you can see it's set up from O2 to O20000. I keep having to change the O20000 since new data is always added. Is there an easy way to automate this? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Question
This example presumes that col AB in PC-01 will dictate the extent:
=SUMPRODUCT((TEXT(OFFSET('PC-01'!$O$2,,,COUNT('PC-01'!AB:AB)),"mmmyy")=TEXT($A11,"mmmyy"))*OFFSET('P C-01'!$AB$2,,,COUNT('PC-01'!AB:AB))) Col AB is presumed to contain a non-numeric header in AB1 (or a blank AB1) -- Max Singapore http://savefile.com/projects/236895 Downloads:16,200 Files:354 Subscribers:53 xdemechanik --- "Secret Squirrel" wrote: I have a summary worksheet that totals up some values on one of my other worksheets. The problem is that I have to keep changing the range of my formula because the data on my other worksheet changes daily and more rows are always added. How can I set up my formula to automatically go to the last row it finds data in on my other worksheet? Here's an example of my formula: =SUMPRODUCT(--(MONTH('PC-01'!$O$2:$O$20000)=MONTH($A11)),--(YEAR('PC-01'!$O$2:$O$20000)=YEAR($A11)),'PC-01'!$AB$2:$AB$20000) As you can see it's set up from O2 to O20000. I keep having to change the O20000 since new data is always added. Is there an easy way to automate this? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Question
Follow up questions:
What if I had another variable in my formula? How would I use these variables with your formula below? Here's my formula with the variable using column C. =SUMPRODUCT(--(InvoicedSales!$A$4:$A$6528=DATE(YEAR($A7),MONTH($ A7),DAY($A7))),--(InvoicedSales!$C$4:$C$6528=1),InvoicedSales!$O$4: $O$6528) Here's my formula using a greater than/less than date range: =SUMPRODUCT(--('PC-01'!$O$2:$O$20000<DATE(YEAR(C1),MONTH(C1),DAY(C1)) ),--('PC-01'!$O$2:$O$20000=DATE(YEAR($A$10),MONTH($A$10),1 )),'PC-01'!$AB$2:$AB$20000) "Max" wrote: This example presumes that col AB in PC-01 will dictate the extent: =SUMPRODUCT((TEXT(OFFSET('PC-01'!$O$2,,,COUNT('PC-01'!AB:AB)),"mmmyy")=TEXT($A11,"mmmyy"))*OFFSET('P C-01'!$AB$2,,,COUNT('PC-01'!AB:AB))) Col AB is presumed to contain a non-numeric header in AB1 (or a blank AB1) -- Max Singapore http://savefile.com/projects/236895 Downloads:16,200 Files:354 Subscribers:53 xdemechanik --- "Secret Squirrel" wrote: I have a summary worksheet that totals up some values on one of my other worksheets. The problem is that I have to keep changing the range of my formula because the data on my other worksheet changes daily and more rows are always added. How can I set up my formula to automatically go to the last row it finds data in on my other worksheet? Here's an example of my formula: =SUMPRODUCT(--(MONTH('PC-01'!$O$2:$O$20000)=MONTH($A11)),--(YEAR('PC-01'!$O$2:$O$20000)=YEAR($A11)),'PC-01'!$AB$2:$AB$20000) As you can see it's set up from O2 to O20000. I keep having to change the O20000 since new data is always added. Is there an easy way to automate this? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Question
The take-away from the earlier response is to effect the dynamic ranges
pegged on a certain key col (eg: Col AB - the col to be summed) to ensure range size consistency. The other presumption besides what was mentioned earlier is that the key col should not have any intervening blanks. So, for your expression: =SUMPRODUCT(--(InvoicedSales!$A$4:$A$6528=DATE(YEAR($A7),MONTH($ A7),DAY($A7))),--(InvoicedSales!$C$4:$C$6528=1),InvoicedSales!$O$4: $O$6528) Indicatively, along the same lines, it could be framed up like this Exp1: OFFSET(InvoicedSales!$A$4,,,Count(InvoicedSales!O: O)) Exp2: OFFSET(InvoicedSales!$C$4,,,Count(InvoicedSales!O: O)) Exp3: OFFSET(InvoicedSales!$O$4,,,Count(InvoicedSales!O: O)) where all the 3 dynamic ranges are pegged to Col O Then you could use: =Sumproduct((Exp1=Cond1)*(Exp1=Cond2)*Exp3) Frame it up likewise for your other expression -- Max Singapore http://savefile.com/projects/236895 Downloads:16,200 Files:354 Subscribers:53 xdemechanik --- "Secret Squirrel" wrote in message ... Follow up questions: What if I had another variable in my formula? How would I use these variables with your formula below? Here's my formula with the variable using column C. =SUMPRODUCT(--(InvoicedSales!$A$4:$A$6528=DATE(YEAR($A7),MONTH($ A7),DAY($A7))),--(InvoicedSales!$C$4:$C$6528=1),InvoicedSales!$O$4: $O$6528) Here's my formula using a greater than/less than date range: =SUMPRODUCT(--('PC-01'!$O$2:$O$20000<DATE(YEAR(C1),MONTH(C1),DAY(C1)) ),--('PC-01'!$O$2:$O$20000=DATE(YEAR($A$10),MONTH($A$10),1 )),'PC-01'!$AB$2:$AB$20000) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Question
I think I have the formula right but I'm getting "0" as a result. Can you
check to see if I wrote this correct? I'm a little unsure if I ahve the second "offset" correct. Is that how I would set up the condition for that second offset? =SUMPRODUCT((TEXT(OFFSET(InvoicedSales!$A$4,,,COUN T(InvoicedSales!O:O)),"dmmmyy")=TEXT($A8,"dmmmyy") )*OFFSET(InvoicedSales!$C$4,,,COUNT(InvoicedSales! O:O))=1)*OFFSET(InvoicedSales!$O$4,,,COUNT(Invoice dSales!O:O)) "Max" wrote: The take-away from the earlier response is to effect the dynamic ranges pegged on a certain key col (eg: Col AB - the col to be summed) to ensure range size consistency. The other presumption besides what was mentioned earlier is that the key col should not have any intervening blanks. So, for your expression: =SUMPRODUCT(--(InvoicedSales!$A$4:$A$6528=DATE(YEAR($A7),MONTH($ A7),DAY($A7))),--(InvoicedSales!$C$4:$C$6528=1),InvoicedSales!$O$4: $O$6528) Indicatively, along the same lines, it could be framed up like this Exp1: OFFSET(InvoicedSales!$A$4,,,Count(InvoicedSales!O: O)) Exp2: OFFSET(InvoicedSales!$C$4,,,Count(InvoicedSales!O: O)) Exp3: OFFSET(InvoicedSales!$O$4,,,Count(InvoicedSales!O: O)) where all the 3 dynamic ranges are pegged to Col O Then you could use: =Sumproduct((Exp1=Cond1)*(Exp1=Cond2)*Exp3) Frame it up likewise for your other expression -- Max Singapore http://savefile.com/projects/236895 Downloads:16,200 Files:354 Subscribers:53 xdemechanik --- "Secret Squirrel" wrote: Follow up questions: What if I had another variable in my formula? How would I use these variables with your formula below? Here's my formula with the variable using column C. =SUMPRODUCT(--(InvoicedSales!$A$4:$A$6528=DATE(YEAR($A7),MONTH($ A7),DAY($A7))),--(InvoicedSales!$C$4:$C$6528=1),InvoicedSales!$O$4: $O$6528) Here's my formula using a greater than/less than date range: =SUMPRODUCT(--('PC-01'!$O$2:$O$20000<DATE(YEAR(C1),MONTH(C1),DAY(C1)) ),--('PC-01'!$O$2:$O$20000=DATE(YEAR($A$10),MONTH($A$10),1 )),'PC-01'!$AB$2:$AB$20000) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Question
The take-away from the earlier response is to effect the dynamic ranges
pegged on a certain key col (eg: Col AB - the col to be summed) to ensure range size consistency. The other presumption besides what was mentioned earlier is that the key col should not have any intervening blanks. So, for your expression: =SUMPRODUCT(--(InvoicedSales!$A$4:$A$6528=DATE(YEAR($A7),MONTH($ A7),DAY($A7))),--(InvoicedSales!$C$4:$C$6528=1),InvoicedSales!$O$4: $O$6528) Indicatively, along the same lines, it could be framed up like this Exp1: OFFSET(InvoicedSales!$A$4,,,Count(InvoicedSales!O: O)) Exp2: OFFSET(InvoicedSales!$C$4,,,Count(InvoicedSales!O: O)) Exp3: OFFSET(InvoicedSales!$O$4,,,Count(InvoicedSales!O: O)) where all the 3 dynamic ranges are pegged to Col O Then you could use: =Sumproduct((Exp1=Cond1)*(Exp1=Cond2)*Exp3) Frame it up likewise for your other expression -- Max Singapore http://savefile.com/projects/236895 Downloads:16,200 Files:354 Subscribers:53 xdemechanik --- "Secret Squirrel" wrote: Follow up questions: What if I had another variable in my formula? How would I use these variables with your formula below? Here's my formula with the variable using column C. =SUMPRODUCT(--(InvoicedSales!$A$4:$A$6528=DATE(YEAR($A7),MONTH($ A7),DAY($A7))),--(InvoicedSales!$C$4:$C$6528=1),InvoicedSales!$O$4: $O$6528) Here's my formula using a greater than/less than date range: =SUMPRODUCT(--('PC-01'!$O$2:$O$20000<DATE(YEAR(C1),MONTH(C1),DAY(C1)) ),--('PC-01'!$O$2:$O$20000=DATE(YEAR($A$10),MONTH($A$10),1 )),'PC-01'!$AB$2:$AB$20000) |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Question
You could try these to replace your 2 earlier expressions posted
Here's my formula with the variable using column C. Try: =SUMPRODUCT((OFFSET(InvoicedSales!$A$4,,,COUNT(Inv oicedSales!O:O))=DATE(YEAR($A7),MONTH($A7),DAY($A7 )))*(OFFSET(InvoicedSales!$C$4,,,COUNT(InvoicedSal es!O:O))=1)*OFFSET(InvoicedSales!$O$4,,,COUNT(Invo icedSales!O:O))) Here's my formula using a greater than/less than date range: Try: =SUMPRODUCT((OFFSET('PC-01'!$O$2,,,COUNT('PC-01'!AB:AB))<DATE(YEAR(C1),MONTH(C1),DAY(C1)))*(OFF SET('PC-01'!$O$2,,,COUNT('PC-01'!AB:AB))=DATE(YEAR($A$10),MONTH($A$10),1))*OFF SET('PC-01'!$AB$2,,,COUNT('PC-01'!AB:AB))) -- Max Singapore http://savefile.com/projects/236895 Downloads: 16,200, Files: 354, Subscribers: 53 xdemechanik --- "Secret Squirrel" wrote in message ... I think I have the formula right but I'm getting "0" as a result. Can you check to see if I wrote this correct? I'm a little unsure if I ahve the second "offset" correct. Is that how I would set up the condition for that second offset? =SUMPRODUCT((TEXT(OFFSET(InvoicedSales!$A$4,,,COUN T(InvoicedSales!O:O)),"dmmmyy")=TEXT($A8,"dmmmyy") )*OFFSET(InvoicedSales!$C$4,,,COUNT(InvoicedSales! O:O))=1)*OFFSET(InvoicedSales!$O$4,,,COUNT(Invoice dSales!O:O)) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula Question | Excel Discussion (Misc queries) | |||
| Question on a Formula | Excel Discussion (Misc queries) | |||
| Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
| Formula question | Excel Discussion (Misc queries) | |||
| Formula Question | Excel Discussion (Misc queries) | |||
Linear Mode
