summing a range if it meets criteria between 2 dates.

[Hammer]

I need to sum a range(columns H1:K6) that meets certain criteria (between to
dates)EX:2007-10-01 thru 2007-10-31.Dates are unknown and not consistant.
My table starts fresh with each fiscal year(october 1). EX:
A B C D E F G H
I J K
1 07/10/01 100.00
50.00
2 07/10/25
25.00 200.00
3 07/10/31 100.00 5.00
25.00
4 07/11/07
500.00 30.00
5 07/11/30
6 07/12/23 100.00 10.00
20.00

 

[Martin Fishlock]

Hi Hammer:

One method is to use the sumproduct formula as shown below.

=SUMPRODUCT(--(MONTH($B$3:$B$8)=10),$H$3:$H$8)
+=SUMPRODUCT(--(MONTH($B$3:$B$8)=10),$I$3:$I$8)
+=SUMPRODUCT(--(MONTH($B$3:$B$8)=10),$J$3:$J$8)
+=SUMPRODUCT(--(MONTH($B$3:$B$8)=10),$K$3:$K$8)

This method is not very easy to maintain so add a helper column to give the
sum and then just do the sumproduct on the helper column.

You can also replace the 10 with a link to cell.


-- Hope this helps
Martin Fishlock, www.nyfconsultants.com, Bangkok, Thailand
Please do not forget to rate this reply.