Sumproduct

[archivesgirl]

I've tried using this formula to count data from colum F where data from
column E is between December 1, 2005 and January 1, 2006. I keep getting
the #VALUE!
I've tried different scenarios with different operators and functions
but to no avail.

=SUMPRODUCT(--('mail-data'!F2:F1002)if('mail-data'!$E$2:$E$1002>DATE(2005,11,30))*--('mail-data'!$E$2:$E$1002<DATE(2006,1,1)))

 

[Bernie Deitrick]

archivesgirl,

For a simple count, you can ignore column F:

=SUMPRODUCT(('mail-data'!$E$2:$E$1002>DATE(2005,11,30))*('mail-data'!$E$2:$E$1002<DATE(2006,1,1)))

But if you have a criteria for column F, you would add that like so:

=SUMPRODUCT(('mail-data'!$F$2:$F$10="Fred")*('mail-data'!$E$2:$E$10>DATE(2005,11,30))*('mail-data'!$E$2:$E$10<DATE(2006,1,1)))


HTH,
Bernie
MS Excel MVP