SUMPRODUCT help

  • Thread starter Thread starter svvm
  • Start date Start date
S

svvm

Hi,
I have three columns. One containing a "value" taken from a list,
another an amount and the third column contains a date.

What I need to do is for a particular value, find out the sum of all
the amounts for a particular month.

This is the formula I have written but the sum is not being calculated
for the particular date range.

Column D Column E Column F
15 March, 2005 23344 UK Perdiem
17 March, 2005 739874 UK Perdiem

=SUMPRODUCT((F5:F150)*(I5:I150="UK Perdiem")*(D5:D150>= "15 March,
2005" & D5:D150<= "31 March, 2005"))

The value shows 0 but if I remove the date calculation it works fine.

Can anybody please help me with the formula?

Thanks in advance
 
svvm said:
Hi,
I have three columns. One containing a "value" taken from a list,
another an amount and the third column contains a date.

What I need to do is for a particular value, find out the sum of all
the amounts for a particular month.

This is the formula I have written but the sum is not being calculated
for the particular date range.

Column D Column E Column F
15 March, 2005 23344 UK Perdiem
17 March, 2005 739874 UK Perdiem

=SUMPRODUCT((F5:F150)*(I5:I150="UK Perdiem")*(D5:D150>= "15 March,
2005" & D5:D150<= "31 March, 2005"))

The value shows 0 but if I remove the date calculation it works fine.

Can anybody please help me with the formula?

Thanks in advance
Click to expand...
=SUMPRODUCT($F$5:$F$150,($I$5:$I$150="UK Perdiem")+0,($D$5:$D$150>=
"15-Mar-05"+0)+0, ($D$5:$D$150<= "31-Mar-05"+0)+0)

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
 
The problem is probably that your date value is not being recognized.
try
=SUMPRODUCT((F5:F150)*(I5:I150="UK Perdiem")*(D5:D150>= datevalue("15 March,
2005") & D5:D150<= datevalue(("31 March, 2005"))
Note with my verison of Excel I had to use "March 15, 2005" You may have to
paly a bit with format.
 
One simple way round the problem of date recognition is to put you
start and end dates in cells and reference the cells in the formula
This has the added benefit of making it very quick and easy t
calculate over any chosen date period
 
Back
Top