Date range in SUMPRODUCT

**filky** · 02-03-2009, 04:32 AM

Hi all,

I am trying to sum figures within a specific date range in the following formulae.

=SUMPRODUCT(--(Bookings!G$7:G$576=B17),--(AND(Bookings!A$7:A$576>=$C$12,Bookings!A$7:A$576<$C$12+7)),Bookings!J$7:J$576)

Currently I get an error and I am certain it is in this part;

(AND(Bookings!A$7:A$576>=$C$12,Bookings!A$7:A$576<$C$12+7))

This is trying to look for dates that are greater than or equal to the one in $C$12 AND less than 7 days later.

Can anyone see why this will not work?

Thanks

Paul

**DonkeyOte** · 02-03-2009, 04:45 AM

one way (the most common)

=SUMPRODUCT(--(Bookings!G$7:G$576=B17),--(Bookings!A$7:A$576>=$C$12),--(Bookings!A$7:A$576<$C$12+7),Bookings!J$7:J$576)

**arthurbr** · 02-03-2009, 04:52 AM

Try
=SUMPRODUCT(--(Bookings!G$7:G$576=B17)*--(Bookings!A$7:A$576>=$C$12)*--(Bookings!A$7:A$576<$C$12+7)*Bookings!J$7:J$576)

**filky** · 02-03-2009, 05:15 AM

Thank you both - I tried both formulae and they each worked. The use of the * was interesting, I have not seen that before.

Thanks for your help.

Kind regards

Paul

**DonkeyOte** · 02-03-2009, 06:39 AM

There's no need to use -- if using * ... if you do you're essentially duplicating the coercion of the Booleans.

If you did opt for * approach you would use:

=SUMPRODUCT((Bookings!G$7:G$576=B17)*(Bookings!A$7:A$576>=$C$12)*(Bookings!A$7:A$576<$C$12+7)*Bookings!J$7:J$576)

Double unary is arguably quicker & is also slightly better in terms of handling the summation range should it contain non-numerics
(ie should any cell in J7:J576 be a string like "Apple" the * approach would generate a #VALUE! error result whereas -- method would not (the Apple entry would simply be ignored)).