sumproduct between two dates and another condition!

[MrDougJ]

Hi,
I have read the threads about using sumproduct as a means to do a sumif with multiple conditions. I am still having a problem with my statement - can anybody help?!
I want to sum the values in a column (J:J) when the following conditions are met:
1)The date (in column B:B) is greater than or equal to the date in AA4
2)The date (in column B:B) is less than or equal to the date in BB4
3)The value in the column C:C is x.

The statement I am trying to use is:
=SUMPRODUCT(--(B:B>=AA4),--(B:B<=BB4),--(C:C="x"), J:J)

I receive a number error - #NUM!

Any ideas? Thanks

 

[SkipVought]

Hi,

You cannot use FULL COLUMN references in SUMPRODUCT...
[tt]
=SUMPRODUCT(--($B$2:$B$65536>=AA4)*($B$2:$B$65536<=BB4)*($C$2:$C$65536="x")*($J$2:$J$65536))
[/tt]


Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]