Offset Formula

[Secret Squirrel]

I'm using the following formula to summarize data from one of my other
worksheets. Right now it's set up to sum the data when the month/year is
equal to my summary tab cell A11. What I want to do is have it sum up the
total when the values are < the month/year in cell A11. How come when I just
change the "=" sign to a "<" it doesn't work? Is it not that easy?

=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)))

 

[Rick Rothstein \(MVP - VB\)]

It doesn't work because you are looking for text items to be less than other
text items.... Apr08 is less than Feb08 when you do that kind of comparison
(this wasn't a problem when you looked for equality). You will need to come
up with text that has a numerical significance if you want to do a less than
kind of test. Maybe have your TEXT function use yyyymm as its output
pattern.

Rick

 

[daddylonglegs]

Because you are using TEXT formula you are comparing text values like Jan08
so you won't get the required results because these will be compared on an
alphabetical (rather than a numerical) basis. Try changing to

=SUMIF(OFFSET('PC-01'!$O$2,,,COUNT('PC-01'!AB:AB)),"<"&$A11,'PC-01'!$AB$2)

Note: that this is looking at specific dates, whatever the format of the
cell concerned

 

[Secret Squirrel]

I assume the "SUMIF" does the same thing as "SUMPRODUCT"?

How would I also add another date range? I want to have it say < A11 but >
than A12.