SUMPRODUCT issue with summing based off date conditions

**guitargod7277** · 06-06-2012, 02:42 PM

Hello everyone,

I am having an issue using the SUMPRODUCT function in the attached scenario (Cell F2) to SUM a set of numbers in range (D5:D32) based off a date range criteria. The criteria range dates are in cells A5:A32, and the conditions are referencing two dates (A2 & B2).

I've tried the formula =SUMPRODUCT((D5:D32)*(A5:A32>=A2)*(A5:A32<=B2)) however I am getting the #VALUE! error, and I'm not sure why. I've used the SUMPRODUCT function successfully as a SUMIFS function in previous scenarios, however I can't make it seem to work on this one.

I am already using the SUMPRODUCT function successfully in cell G2 to get the count based off the necessary conditions; but I need the sum to calculate an average.

Any help guys on whay I am doing wrong? Or what I should do to get the sum of D5:D32 based off the dates in A5:A32 using the 2 dates in A2 & B2 as criteria?

Workbook attached.

I appreciate it

**dilipandey** · 06-06-2012, 02:53 PM

Hi GuitarGod,

Welcome to the forum.

Use the below formula:-

{=SUM(IF(($B$5:$B32>=$A$2)*($B$5:$B32<=$B$2),($D$5:$D32),""))}
SUMPRODUCT issue.xlsx

Above is an array formula and need to be entered using ctrl + Shift + Enter key combination..thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**Pepe Le Mokko** · 06-06-2012, 02:59 PM

As your ranges also contain time, when you try to get results after or equal 4/4 and before or equal to 5/4 you will only get the data for 4/4. Is that what you want?

**Haseeb Avarakkan** · 06-06-2012, 03:01 PM

If you are looking DATE part only in column A, use SUMIF instead of SUMPRODUCT

F2:

=SUMIF(A5:A32,">="&A2,D5:D32)-SUMIF(A5:A32,">="&B2+1,D5:D32)

G2:

=SUMPRODUCT((A5:A32>=A2)*(A5:A32<B2+1)*(D5:D32<>""))

You could also use SUMIFS & COUNTIFS respectively.

**Pepe Le Mokko** · 06-06-2012, 03:10 PM

The cause of the #VALUE is because your original formula tries to use "null
values" (presumably null strings, not empty cells) in the arithmetic
expression.

**daddylonglegs** · 06-06-2012, 03:24 PM

Originally Posted by Pepe Le Mokko

The cause of the #VALUE is because your original formula tries to use "null
values" (presumably null strings, not empty cells) in the arithmetic
expression.

....and you can fix that by not multiplying by the sum range, i.e. use

=SUMPRODUCT(D5:D32,(A5:A32>=A2)*(A5:A32<=B2))

....although that formula returns zero for your example because there are no rows which meet both criteria....but it'll work if you have some different dates....

**guitargod7277** · 06-06-2012, 05:22 PM

Thanks guys for your expertise! All of your solutions worked. I appreciate those who helped me within the same function (SUMPRODUCT) I am trying to get acclimated to. Shoutout to Daddylonglegs.

Cheers!