SUMPRODUCT, OR, Less than Month & Less than Year

Hello,

I am attempting to use a modified SUMPRODUCT formula that is not giving me the results I would like. The formula is as follows:

=SUMPRODUCT((('PTDE ITD 2.22.2013'!$K$15:$K$1107<=MONTH($AQ$2))+('PTDE ITD 2.22.2013'!$J$15:$J$1107<YEAR($AQ$2)))*('PTDE ITD 2.22.2013'!$V$15:$V$1107=$AN4)*('PTDE ITD 2.22.2013'!$S$15:$S$1107))

The <= months is working but the <year is not. What syntax modification must I make? FYI, the "+" represents an "OR" in this case.

Any and all help is MUCH appreciated.

-JUGG

Additionally, the years in 'PTDE ITD 2.22.2013'!$J$15:$J$1107 are in the format "2013". I suspect this may have something to do with it.

Dilipandy,

Thanks for your reply. All of the 2013's are formatted as numbers. I copied and rewrote a portion of the formula to test if the YEAR portion was executing and it is not. Here's how I tested it:

=SUMPRODUCT(('PTDE ITD 2.22.2013'!$J$15:$J$1107<YEAR($AQ$2))*('PTDE ITD 2.22.2013'!$V$15:$V$1107=$AN4)*('PTDE ITD 2.22.2013'!$S$15:$S$1107))

It is returning a value of 0.

Admittedly, I am new to using dates in formulas and it is probably evident here. haha

DILI,

I would certainly upload a sample workbook however this is being done on my work computer and we are not able to upload. :-(

=SUMPRODUCT(('PTDE ITD 2.22.2013'!$J$15:$J$1107<YEAR($AQ$2)*1) is giving me 0.

Thanks a MILLION for helping me with this.

Originally Posted by JosephP

what does
=SUM('PTDE ITD 2.22.2013'!$J$15:$J$1107)
return?

Joseph,

Thanks a lot for your reply! Strikingly, that formula returned a big 0. There were errors in all of those cells indicating that it was a number stored as text so I converted them all to numbers. Now that formula returns a large number. That's a step in the right direction!

Thanks, brother!

Originally Posted by dilipandey

I already guessed that..

so it is always better to post a sample workbook...

Regards,
DILIPandey
<click on below * if this helps>

DILI,

You did guess that and I appreciate it, brother. I thought that I had formatted them as numbers but apparently they were still stored as text. Kudos to you both.