Formula Question

  • Thread starter Thread starter Secret Squirrel
  • Start date Start date
S

Secret Squirrel

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?
 
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 said:
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
Click to expand...
this?
 
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('PC-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
 
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)
 
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)
Click to expand...

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
---
 
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,,,COUNT(InvoicedSales!O:O)),"dmmmyy")=TEXT($A8,"dmmmyy"))*OFFSET(InvoicedSales!$C$4,,,COUNT(InvoicedSales!O:O))=1)*OFFSET(InvoicedSales!$O$4,,,COUNT(InvoicedSales!O:O))
 
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)
Click to expand...

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
---
 
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(InvoicedSales!O:O))=DATE(YEAR($A7),MONTH($A7),DAY($A7)))*(OFFSET(InvoicedSales!$C$4,,,COUNT(InvoicedSales!O:O))=1)*OFFSET(InvoicedSales!$O$4,,,COUNT(InvoicedSales!O:O)))

Here's my formula using a greater than/less than date range:
Click to expand...

Try:
=SUMPRODUCT((OFFSET('PC-01'!$O$2,,,COUNT('PC-01'!AB:AB))<DATE(YEAR(C1),MONTH(C1),DAY(C1)))*(OFFSET('PC-01'!$O$2,,,COUNT('PC-01'!AB:AB))>=DATE(YEAR($A$10),MONTH($A$10),1))*OFFSET('PC-01'!$AB$2,,,COUNT('PC-01'!AB:AB)))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 16,200, Files: 354, Subscribers: 53
xdemechanik
---
 
Back
Top