SumProduct using Date Range
 

I need to sum a values using multiple criteria and a date range.

Example - I need to sum the total amount of all investors that have an "N"
in the n/e column, are in a specified region, in a specified fund, and are
between the dates of 2/1/2010 through 3/1/10. Can't seem to get this to
work? Any help is greatly appreciated.

n/e Inv Date Investors Amount Fund Region
E 2/1/2010 Client 1 $250,000 1 Asia
E 2/1/2010 Client 2 $20,000 2 North America
N 2/1/2010 Client 3 $14,000 4 North America
N 2/1/2010 Client 4 $11,000 10 Asia
E 2/1/2010 Client 5 $7,000,000 9 Europe
E 2/1/2010 Client 6 $6,500 6 Europe
E 2/1/2010 Client 7 $5,000,000 5 Asia
N 2/1/2010 Client 8 $4,000 2 Canda
E 2/1/2010 Client 9 $2,500,000 2 Canda

SumProduct using Date Range
 

Use cells to hold the criteria.

A1 = N
B1 = a specified region
C1 = a specified fund
D1 = 2/1/2010
E1 = 3/1/10

Then:

=SUMPRODUCT(--(n/e=A1),--(Region=B1),--(Fund=C1),--(Date=d1),--(Date<=E1),range_to_sum)


--
Biff
Microsoft Excel MVP


"NMK" wrote in message
...
I need to sum a values using multiple criteria and a date range.

Example - I need to sum the total amount of all investors that have an "N"
in the n/e column, are in a specified region, in a specified fund, and are
between the dates of 2/1/2010 through 3/1/10. Can't seem to get this to
work? Any help is greatly appreciated.

n/e Inv Date Investors Amount Fund Region
E 2/1/2010 Client 1 $250,000 1 Asia
E 2/1/2010 Client 2 $20,000 2 North America
N 2/1/2010 Client 3 $14,000 4 North America
N 2/1/2010 Client 4 $11,000 10 Asia
E 2/1/2010 Client 5 $7,000,000 9 Europe
E 2/1/2010 Client 6 $6,500 6 Europe
E 2/1/2010 Client 7 $5,000,000 5 Asia
N 2/1/2010 Client 8 $4,000 2 Canda
E 2/1/2010 Client 9 $2,500,000 2 Canda