SUM Data Between 2 Dates

[TRI0N]

Okay now lets say 'Transaction DB'J:J contains the dates we are searching against. $A contains the Product ID Number and 'Transaction DB'AD:AD contains the total that needs to be summed.

I want to pull information between 10/1/2009 thru 10/31/2009 that match Item Number (from $A on same sheet) to sum the Totals in 'Transaction DB'AD:AD that match all criteria above.

What I've been trying to use:
{=SUMPRODUCT(‐‐('Transaction DB'!J:J,">="&DATE('Set Date'!$D$2,'Set Date'!$B$2,'Set Date'!$C$2)),‐‐('Transaction DB'!J:J,"<="&DATE('Set Date'!$D$4,'Set Date'!$B$4,'Set Date'!$C$4)),--('Transaction DB'!D:D,A5),('Transaction DB'!$AD:$AD))}

I get:
#NAME?

What gives?

[NBVC]

If your are using XL2007, then

=SUMPRODUCT(--('Transaction DB'!J:J>=DATE('Set Date'!$D$2,'Set Date'!$B$2,'Set Date'!$C$2)),--('Transaction DB'!J:J<=DATE('Set Date'!$D$4,'Set Date'!$B$4,'Set Date'!$C$4)),--('Transaction DB'!D:D=A5),'Transaction DB'!$AD:$AD)

otherwise you need defined ranges, not whole ranges...

e.g

=SUMPRODUCT(--('Transaction DB'!$J$1:$J$100>=DATE('Set Date'!$D$2,'Set Date'!$B$2,'Set Date'!$C$2)),--('Transaction DB'!$J$1:$J$100<=DATE('Set Date'!$D$4,'Set Date'!$B$4,'Set Date'!$C$4)),--('Transaction DB'!$D$1:$D$100=A5),'Transaction DB'!$AD$1:$AD$100)

[Richard Buttrey]

Hi,

You can't use whole column references with SUMPRODUCT(). You need to specify the A1 type address. e.g.

'Transaction DB'!J2:J100," .....

and keep the range to just the data since these array functions are heavy on processing and take exponentially longer as the number of rows increases. Make sure all the ranges are of a consistent length. i.e. J2:J100 & AD2:AD100 not AD2:AD101 or something else.

HTH

[TRI0N]

I'm using 2007 of course..

[Richard Buttrey]

I use specific ranges, you can only use whole columns in SUMPRODUCT if you are using Excel 2007......

Thanks ddl. I didn't know that had changed for 2007. Another one to file away.

Cheers.

[gurpreet161]

Can you please put the formula in the excel file and provide me with the file? It doesn't seem to work for me.