summing a range if it meets criteria between 2 dates.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Hammer - 27 Apr 2008 00:16 GMT

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

Reply to this Message

Martin Fishlock - 27 Apr 2008 04:20 GMT

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.

> 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.
[quoted text clipped - 12 lines]
> 6 07/12/23 100.00 10.00
> 20.00

Reply to this Message