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('P C-01'!$AB$2,,,COUNT('PC-01'!AB:AB)))

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


"Secret Squirrel" wrote in
message ...
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('P C-01'!$AB$2,,,COUNT('PC-01'!AB:AB)))

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" wrote:

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('P C-01'!$AB$2,,,COUNT('PC-01'!AB:AB)))

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.

"daddylonglegs" wrote:

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" wrote:

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('P C-01'!$AB$2,,,COUNT('PC-01'!AB:AB)))