SUMPRODUCT for range of dates

[Ashinda]

Hi all,

I've had a look through MrExcel (and other websites) and I'm struggling to adapt the answers found there to my problem.

I currently have the following that refers to a single date (K2):

[FONT=Calibri][SIZE=3][COLOR=#000000]=IF(SUMPRODUCT( -- ($J$2 = Data'!$Y$2:$Y$5000), -- (AF5= Data!$AP$2:$AP$5000),('Data'!$AQ$2:$AQ$5000))<1,"",SUMPRODUCT(-- ($J$2 = Data!$Y$2:$Y$5000), -- (AF5= Data!$AP$2:$AP$5000),(Data!$AQ$2:$AQ$5000)))[/COLOR][/SIZE][/FONT]

However I need to change the formula so that it ranges from one date (K2) to another (let's go with P2).

Is it possible to adapt the formula I currently use or will I have to go back to square one?

Thank in advance

 

[Ashinda]

Sorry I meant J2 instead of K2 (I'm new to MrExcel so I'm not 100% sure how to edit my post)...

 

[Aladin Akyurek]

Why not just this?

=MAX(0,SUMPRODUCT(

'Data'!$AQ$2:$AQ$5000,​

--($J$2=Data'!$Y$2:$Y$5000),--(AF5=Data!$AP$2:$AP$5000))​

Or this which is faster?

=MAX(0,SUMIFS(

'Data'!$AQ$2:$AQ$5000,​

Data'!$Y$2:$Y$5000,

$J$2​

,Data!$AP$2:$AP$5000,

AF5)​

​

<strike></strike>

 

[Ashinda]

Hey Aladin,

Thanks for your quick response.

That works fine for referencing the one date, but if I want it to range over a certain period of time, say from 23/06/2018 (J2) to the 15/07/2018 (P2)

How would I incorporate this into the formula given?

Also, I love that you've been able to put SUMIFS, means I can just reference the entire column now. Living the dream.

 

[Aladin Akyurek]

Hey Aladin,

Thanks for your quick response.

That works fine for referencing the one date, but if I want it to range over a certain period of time, say from 23/06/2018 (J2) to the 15/07/2018 (P2)

How would I incorporate this into the formula given?

Also, I love that you've been able to put SUMIFS, means I can just reference the entire column now. Living the dream.

Like this:

=MAX(0,SUMIFS('Data'!$AQ$2:$AQ$5000,Data'!$Y$2:$Y$5000,">="&$J$2,Data'!$Y$2:$Y$5000,"<="&$P$2,Data!$AP$2:$AP$5000,AF5)​

 

[Ashinda]

Like this:

=MAX(0,SUMIFS('Data'!$AQ$2:$AQ$5000,Data'!$Y$2:$Y$5000,">="&$J$2,Data'!$Y$2:$Y$5000,"<="&$P$2,Data!$AP$2:$AP$5000,AF5)​

It works great, thanks for your help. Now to just adapt it for the other columns... Fun never stops.

 

[Aladin Akyurek]

It works great, thanks for your help. Now to just adapt it for the other columns... Fun never stops.

You are welcome. Thanks for providing feedback.