Prev: SUMPRODUCT with date range
Next: Summing multi-level bill of material
From: NMK on 19 May 2010 16:55
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

From: T. Valko on 19 May 2010 17:02
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" <NMK(a)discussions.microsoft.com> wrote in message
news:FD8EE345-BD1D-4677-A828-2F2439461D10(a)microsoft.com...
>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
>


 | 
Pages: 1
Prev: SUMPRODUCT with date range
Next: Summing multi-level bill of material