Sum Of Values From A Date Range

**Bob McCusker** · 11-10-2009, 12:47 AM

Hey guys,

I'm having a problem with a formula I'm using to sum the values within a certain date range. They have to meet certain criteria also, but I have that part of the formula worked out already. Here is the formula I am using, with the part I believe is the problem in red:

=SUMPRODUCT(--([Register.xls]DATA!$B$2:$B$10000>=DATE(2009,11,1)),--([Register.xls]DATA!$B$2:$B$10000<=DATE
(2009,11,30)),--([Register.xls]DATA!$C$2:$C$10000=XTD!$E$100),--([Register.xls]DATA!$F$2:$F$10000=XTD!$A$106),
--([Register.xls]DATA!$G$2:$G$10000=XTD!$B$107),--([Register.xls]DATA!$H$2:$H$10000=XTD!$C$108),Register.xls]DA
TA!$J$2:$J$10000)

I searched around and tried replacing the ",--" with asterisks, I tried holding CTRL+SHIFT and pressing ENTER to activate it, and I just can't figure it out. Anybody understand what might be going wrong?

BTW big thanks to you guys for even helping me get to a point where I can attempt to write a formula like this!

**teylyn** · 11-10-2009, 12:55 AM

What result are you getting? 0? #Ref! ??

Or maybe it is the missing [ bracket in your last array. see correction attempt below.

Please Login or Register  to view this content.

hth

**Bob McCusker** · 11-10-2009, 01:07 AM

Wow I feel like an idiot, I have been looking this over for around 3 hours now and didn't see what was wrong until I just looked at it again. Sorry to waste your time, that bracket is there in my formula don't know why it didn't paste, but one of my arrays had the wrong info in it.

**DonkeyOte** · 11-10-2009, 04:17 AM

Bob McCusker, I'm glad you've resolved but FWIW you should wary of using SUMPRODUCT formulae over such extensive ranges ... the below

Please Login or Register  to view this content.

is referencing close on 70,000 cells ... given the nature of a SUMPRODUCT (akin to a traditional CSE Array) that's a LOT of cells... so try to keep ranges as lean as possible.

I note the source data is in an external file... because of that your options to convert the above into something more efficient are limited based on whether or not the target file is always open when this calculation is performed (?)

Regardless I would advise you create a concatenation column in [Regsiter.xls] so as to reduce number of cells being reviewed in the SUMPRODUCT... for ex. if we assume you're always conducting monthly analysis then:

Please Login or Register  to view this content.

You can then utilise the above column in your SUMPRODUCT in replacement of the earlier version such that the summation formula becomes

Please Login or Register  to view this content.

The above is now processing far fewer cells than before (20000 rather than 70000)

If the file is always open you could adopt a similar approach to the above but use SUMIF rather than SUMPRODUCT which would be significantly more efficient.