SUMPRODUCT with MAX Date

Hopeless Cub Fan · May 25, 2011

I have a SUMPRODUCT formula that is working the way it is intended to. It is pretty straight forward.

  =SUMPRODUCT(--($G$2:$G$2687=$G14),--($J$2:$J$2687=$J14),--($L$2:$L$2687=$L14),--($O$2:$O$2687=2),--($R$2:$R$2687<date(2011,4,1)), $s$2:$s$2687)="" <date(2011,4,1)),="" $s$2:$s$2687)
</date(2011,4,1)),>< DATE(2011,4,1)), $S$2:$S$2687)
<date(2011,4,1)), $s$2:$s$2687)=""
<date(2011,4,1)), $s$2:$s$2687)="" <date(2011,4,1)),="" $s$2:$s$2687)
<date(2011,4,1)), $s$2:$s$2687)<="" font=""><date(2011,4,1)), $s$2:$s$2687)
<date(2011,4,1)), $s$2:$s$2687)

What I am doing is looking at our system pricing and trying to find the last system price for each unique combination prior to a specific date. Each line of data is unique and the unique values are comprised of dist channel, ship-to, material, incoterms, valid from/to date and price.

In the sample formula above I am looking for the formula to return the sum of all values with a “Valid To” date less than 4/1/2011. The formula does this by returning a value of $383 (186 for 12/31/2010 and 197 for 3/31/2010) from the sample data below.

The issue is now I just want the maximum date that is less than 4/1/2011. So using the sample data below I would want to see $197 as it is the most recent price based on my date threshold of 4/1/2011.

This is an extremely small sample set of data that hopefully conveys what I am trying to do. I have been trying to incorporate a MAX function on the date, but have not been successful. Any help you can offer would be much appreciated! Thanks!

 DChl Ship-to Mat’l Inco Created on Valid From Valid to Amount
2 32056 190163 2 1/10/2011 11/1/2010 12/31/2010 186.00
2 32056 190163 2 1/10/2011 1/1/2011 3/31/2011 197.00
2 32056 190163 2 3/10/2011 4/1/2011 12/31/9999 205.00</date(2011,4,1)),></date(2011,4,1)),></date(2011,4,1)),></date(2011,4,1)),></date(2011,4,1)),>

T. Valko · May 25, 2011

Hopeless Cub Fan said:
I have a SUMPRODUCT formula that is working the way it is intended to. It is pretty straight forward.

=SUMPRODUCT(--($G$2:$G$2687=$G14),--($J$2:$J$2687=$J14),--($L$2:$L$2687=$L14),--($O$2:$O$2687=2),--($R$2:$R$2687<DATE(2011,4,1)), p <date(2011,4,1)),="" $s$2:$s$2687)="" $s$2:$s$2687)<> </DATE(2011,4,1)),>< DATE(2011,4,1)), $S$2:$S$2687)

<DATE(2011,4,1)), p $s$2:$s$2687)="" <>
<DATE(2011,4,1)), p <date(2011,4,1)),="" $s$2:$s$2687)="" $s$2:$s$2687)<><DATE(2011,4,1)), font="" $s$2:$s$2687)<=""><DATE(2011,4,1)), font $s$2:$s$2687)<>
<DATE(2011,4,1)), font $s$2:$s$2687)<>

What I am doing is looking at our system pricing and trying to find the last system price for each unique combination prior to a specific date. Each line of data is unique and the unique values are comprised of dist channel, ship-to, material, incoterms, valid from/to date and price.

In the sample formula above I am looking for the formula to return the sum of all values with a “Valid To” date less than 4/1/2011. The formula does this by returning a value of $383 (186 for 12/31/2010 and 197 for 3/31/2010) from the sample data below.

The issue is now I just want the maximum date that is less than 4/1/2011. So using the sample data below I would want to see $197 as it is the most recent price based on my date threshold of 4/1/2011.

This is an extremely small sample set of data that hopefully conveys what I am trying to do. I have been trying to incorporate a MAX function on the date, but have not been successful. Any help you can offer would be much appreciated! Thanks!

DChl Ship-to Mat’l Inco Created on Valid From Valid to Amount
2 32056 190163 2 1/10/2011 11/1/2010 12/31/2010 186.00
2 32056 190163 2 1/10/2011 1/1/2011 3/31/2011 197.00
2 32056 190163 2 3/10/2011 4/1/2011 12/31/9999 205.00</DATE(2011,4,1)),></DATE(2011,4,1)),></DATE(2011,4,1)),></DATE(2011,4,1)),></DATE(2011,4,1)),>

How about using an intermediate cell to extract that max date?

Array entered**:

=MAX(IF(R2:R2687 < <?XML:NAMESPACE PREFIX = DATE(2011,4,1),R2 /><DATE(2011,4,1),R2:R2687))< p>DATE(2011,4,1),R2:R2687))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Let's assume that formula is in cell A1.

Then you can simply refer to cell A1 in your SUMPRODUCT formula.

</DATE(2011,4,1),R2:R2687))<>

Hopeless Cub Fan · May 26, 2011

Valko,

Thanks for your help. It appears that your solution works, but only for those records that match the overall max date. I believe that this array formula returns the max date for all my data, not the MAX date for each unique dist channel, ship-to, material, incoterms combo.

Using your suggestion above I was trying to find the MAX date for each dist channel, ship-to, material, incoterms combo using the array formula below, but it isn’t working exactly as planned. 80% of the time it appears that it returns the right date, but then there are other records where it returns a value of 0 (date 1/0/1900).

Formula from cell X4 in sample data set entered as an array:
=MAX(IF(($V$3:$V$14) < DATE(2011,4,1)*($S$3:$S$14=S4)*($P$3:$P$14=P4)*($R$3:$R$14=R4)*($T$3:$T$14=2),$V$3:$V$14))<date(2011,4,1)*($s$3:$s$14=s4)*($p$3:$p$14=p4)*($r$3:$r$14=r4)*($t$3:$t$14=2),$v$3:$v$14))

I am looking through the individual fields and cannot figure out for the life of me why a zero is coming back. Below is a screen shot of what I am seeing I highlighted in yellow the records that should be returned. The formula above was used on the very small sample data set below.

Excel 2007<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th><th>Y</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">2</td><td style="border-bottom: 1px solid black;;">DChl</td><td style="border-bottom: 1px solid black;;">Customer</td><td style="border-bottom: 1px solid black;;">Ship-to</td><td style="border-bottom: 1px solid black;;">Material</td><td style="border-bottom: 1px solid black;;">Inc</td><td style="border-bottom: 1px solid black;;">Valid From</td><td style="border-bottom: 1px solid black;;">Valid to</td><td style="border-bottom: 1px solid black;;">Amount</td><td style=";">MAX Date</td><td style="text-align: right;background-color: #EAF1F6;;">
</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;;">4</td><td style="border-top: 1px solid black;;">21867</td><td style="border-top: 1px solid black;;">27946</td><td style="border-top: 1px solid black;;">C422370</td><td style="border-top: 1px solid black;border-right: 1px solid black;;">2</td><td style="border-top: 1px solid black;border-left: 1px solid black;;">10/1/2010</td><td style="border-top: 1px solid black;;">10/31/2010</td><td style="text-align: right;border-top: 1px solid black;;">236.00</td><td style="text-align: right;;">1/23/2011</td><td style="text-align: right;background-color: #EAF1F6;;">
</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="background-color: #FFFF00;;">4</td><td style="background-color: #FFFF00;;">21867</td><td style="background-color: #FFFF00;;">27946</td><td style="background-color: #FFFF00;;">C422370</td><td style="border-right: 1px solid black;background-color: #FFFF00;;">2</td><td style="border-left: 1px solid black;background-color: #FFFF00;;">11/1/2010</td><td style="background-color: #FFFF00;;">1/23/2011</td><td style="text-align: right;background-color: #FFFF00;;">215.00</td><td style="text-align: right;background-color: #FFFF00;;">1/23/2011</td><td style="background-color: #FFFF00;;">CORECT</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style=";">4</td><td style=";">21867</td><td style=";">27946</td><td style=";">C422370</td><td style="border-right: 1px solid black;;">2</td><td style="border-left: 1px solid black;;">1/24/2011</td><td style=";">4/14/2011</td><td style="text-align: right;;">220.00</td><td style="text-align: right;;">1/23/2011</td><td style="text-align: right;background-color: #EAF1F6;;">
</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style=";">4</td><td style=";">21867</td><td style=";">27946</td><td style=";">C422370</td><td style="border-right: 1px solid black;;">2</td><td style="border-left: 1px solid black;;">4/15/2011</td><td style=";">4/24/2011</td><td style="text-align: right;;">231.00</td><td style="text-align: right;;">1/23/2011</td><td style="text-align: right;background-color: #EAF1F6;;">
</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style="border-bottom: 1px solid black;;">4</td><td style="border-bottom: 1px solid black;;">21867</td><td style="border-bottom: 1px solid black;;">27946</td><td style="border-bottom: 1px solid black;;">C422370</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;;">2</td><td style="border-bottom: 1px solid black;border-left: 1px solid black;;">4/25/2011</td><td style="border-bottom: 1px solid black;;">12/31/9999</td><td style="text-align: right;border-bottom: 1px solid black;;">231.00</td><td style="text-align: right;;">1/23/2011</td><td style="text-align: right;background-color: #EAF1F6;;">
</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style="border-top: 1px solid black;;">8</td><td style="border-top: 1px solid black;;">21867</td><td style="border-top: 1px solid black;;">22864</td><td style="border-top: 1px solid black;;">400889</td><td style="border-top: 1px solid black;border-right: 1px solid black;;">1</td><td style="border-top: 1px solid black;border-left: 1px solid black;;">11/1/2010</td><td style="border-top: 1px solid black;;">11/3/2010</td><td style="text-align: right;border-top: 1px solid black;;">493.00</td><td style="text-align: right;;">1/0/1900</td><td style="text-align: right;background-color: #EAF1F6;;">
</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style=";">8</td><td style=";">21867</td><td style=";">22864</td><td style=";">400889</td><td style="border-right: 1px solid black;;">1</td><td style="border-left: 1px solid black;;">11/4/2010</td><td style=";">12/7/2010</td><td style="text-align: right;;">493.00</td><td style="text-align: right;;">1/0/1900</td><td style="text-align: right;background-color: #EAF1F6;;">
</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style=";">8</td><td style=";">21867</td><td style=";">22864</td><td style=";">400889</td><td style="border-right: 1px solid black;;">1</td><td style="border-left: 1px solid black;;">12/8/2010</td><td style=";">12/19/2010</td><td style="text-align: right;;">406.00</td><td style="text-align: right;;">1/0/1900</td><td style="text-align: right;background-color: #EAF1F6;;">
</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style="background-color: #FFFF00;;">8</td><td style="background-color: #FFFF00;;">21867</td><td style="background-color: #FFFF00;;">22864</td><td style="background-color: #FFFF00;;">400889</td><td style="border-right: 1px solid black;background-color: #FFFF00;;">1</td><td style="border-left: 1px solid black;background-color: #FFFF00;;">12/20/2010</td><td style="background-color: #FFFF00;;">3/31/2011</td><td style="text-align: right;background-color: #FFFF00;;">406.00</td><td style="text-align: right;background-color: #FFFF00;;">1/0/1900</td><td style="background-color: #FFFF00;;">WHY 0?</td></tr><tr><td style="color: #161120;text-align: center;">12</td><td style=";">8</td><td style=";">21867</td><td style=";">22864</td><td style=";">400889</td><td style="border-right: 1px solid black;;">1</td><td style="border-left: 1px solid black;;">4/1/2011</td><td style=";">4/14/2011</td><td style="text-align: right;;">418.00</td><td style="text-align: right;;">1/0/1900</td><td style="text-align: right;background-color: #EAF1F6;;">
</td></tr><tr><td style="color: #161120;text-align: center;">13</td><td style=";">8</td><td style=";">21867</td><td style=";">22864</td><td style=";">400889</td><td style="border-right: 1px solid black;;">1</td><td style="border-left: 1px solid black;;">4/15/2011</td><td style=";">4/30/2011</td><td style="text-align: right;;">426.00</td><td style="text-align: right;;">1/0/1900</td><td style="text-align: right;background-color: #EAF1F6;;">
</td></tr><tr><td style="color: #161120;text-align: center;">14</td><td style="border-bottom: 1px solid black;;">8</td><td style="border-bottom: 1px solid black;;">21867</td><td style="border-bottom: 1px solid black;;">22864</td><td style="border-bottom: 1px solid black;;">400889</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;;">1</td><td style="border-bottom: 1px solid black;border-left: 1px solid black;;">5/1/2011</td><td style="border-bottom: 1px solid black;;">12/31/9999</td><td style="text-align: right;border-bottom: 1px solid black;;">450.00</td><td style="text-align: right;;">1/0/1900</td><td style="text-align: right;background-color: #EAF1F6;;">
</td></tr></tbody></table>

Sheet1

I imagine that if I could get this formula to return the correct date instead of zero then I could probably use it in the SUMPRODUCT formula as you had suggested.
</date(2011,4,1)*($s$3:$s$14=s4)*($p$3:$p$14=p4)*($r$3:$r$14=r4)*($t$3:$t$14=2),$v$3:$v$14))

T. Valko · May 26, 2011

Hopeless Cub Fan said:
Valko,

Thanks for your help. It appears that your solution works, but only for those records that match the overall max date. I believe that this array formula returns the max date for all my data, not the MAX date for each unique dist channel, ship-to, material, incoterms combo.

Using your suggestion above I was trying to find the MAX date for each dist channel, ship-to, material, incoterms combo using the array formula below, but it isn’t working exactly as planned. 80% of the time it appears that it returns the right date, but then there are other records where it returns a value of 0 (date 1/0/1900).

Formula from cell X4 in sample data set entered as an array:

=MAX(IF(($V$3:$V$14) < DATE(2011,4,1)*($S$3:$S$14=S4)*($P$3:$P$14=P4)*($R$3:$R$14=R4)*($T$3:$T$14=2),$V$3:$V$14))<?xml:namespace prefix = date(2011,4,1)*($s$3 /><date(2011,4,1)*($s$3:$s$14=s4)*($p$3:$p$14=p4)*($r$3:$r$14=r4)*($t$3:$t$14=2),$v$3:$v$14))< p>

I am looking through the individual fields and cannot figure out for the life of me why a zero is coming back. Below is a screen shot of what I am seeing I highlighted in yellow the records that should be returned. The formula above was used on the very small sample data set below.

Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH>

</TH><TH>P</TH><TH>Q</TH><TH>R</TH><TH>S</TH><TH>T</TH><TH>U</TH><TH>V</TH><TH>W</TH><TH>X</TH><TH>Y</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="BORDER-BOTTOM: black 1px solid">DChl</TD><TD style="BORDER-BOTTOM: black 1px solid">Customer</TD><TD style="BORDER-BOTTOM: black 1px solid">Ship-to</TD><TD style="BORDER-BOTTOM: black 1px solid">Material</TD><TD style="BORDER-BOTTOM: black 1px solid">Inc</TD><TD style="BORDER-BOTTOM: black 1px solid">Valid From</TD><TD style="BORDER-BOTTOM: black 1px solid">Valid to</TD><TD style="BORDER-BOTTOM: black 1px solid">Amount</TD><TD>MAX Date</TD><TD style="BACKGROUND-COLOR: #eaf1f6; TEXT-ALIGN: right">

</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP: black 1px solid">4</TD><TD style="BORDER-TOP: black 1px solid">21867</TD><TD style="BORDER-TOP: black 1px solid">27946</TD><TD style="BORDER-TOP: black 1px solid">C422370</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid">2</TD><TD style="BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid">10/1/2010</TD><TD style="BORDER-TOP: black 1px solid">10/31/2010</TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right">236.00</TD><TD style="TEXT-ALIGN: right">1/23/2011</TD><TD style="BACKGROUND-COLOR: #eaf1f6; TEXT-ALIGN: right">

</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="BACKGROUND-COLOR: #ffff00">4</TD><TD style="BACKGROUND-COLOR: #ffff00">21867</TD><TD style="BACKGROUND-COLOR: #ffff00">27946</TD><TD style="BACKGROUND-COLOR: #ffff00">C422370</TD><TD style="BORDER-RIGHT: black 1px solid; BACKGROUND-COLOR: #ffff00">2</TD><TD style="BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00">11/1/2010</TD><TD style="BACKGROUND-COLOR: #ffff00">1/23/2011</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">215.00</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">1/23/2011</TD><TD style="BACKGROUND-COLOR: #ffff00">CORECT</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD>4</TD><TD>21867</TD><TD>27946</TD><TD>C422370</TD><TD style="BORDER-RIGHT: black 1px solid">2</TD><TD style="BORDER-LEFT: black 1px solid">1/24/2011</TD><TD>4/14/2011</TD><TD style="TEXT-ALIGN: right">220.00</TD><TD style="TEXT-ALIGN: right">1/23/2011</TD><TD style="BACKGROUND-COLOR: #eaf1f6; TEXT-ALIGN: right">

</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD>4</TD><TD>21867</TD><TD>27946</TD><TD>C422370</TD><TD style="BORDER-RIGHT: black 1px solid">2</TD><TD style="BORDER-LEFT: black 1px solid">4/15/2011</TD><TD>4/24/2011</TD><TD style="TEXT-ALIGN: right">231.00</TD><TD style="TEXT-ALIGN: right">1/23/2011</TD><TD style="BACKGROUND-COLOR: #eaf1f6; TEXT-ALIGN: right">

</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="BORDER-BOTTOM: black 1px solid">4</TD><TD style="BORDER-BOTTOM: black 1px solid">21867</TD><TD style="BORDER-BOTTOM: black 1px solid">27946</TD><TD style="BORDER-BOTTOM: black 1px solid">C422370</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-BOTTOM: black 1px solid">2</TD><TD style="BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid">4/25/2011</TD><TD style="BORDER-BOTTOM: black 1px solid">12/31/9999</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">231.00</TD><TD style="TEXT-ALIGN: right">1/23/2011</TD><TD style="BACKGROUND-COLOR: #eaf1f6; TEXT-ALIGN: right">

</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD style="BORDER-TOP: black 1px solid">8</TD><TD style="BORDER-TOP: black 1px solid">21867</TD><TD style="BORDER-TOP: black 1px solid">22864</TD><TD style="BORDER-TOP: black 1px solid">400889</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid">1</TD><TD style="BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid">11/1/2010</TD><TD style="BORDER-TOP: black 1px solid">11/3/2010</TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right">493.00</TD><TD style="TEXT-ALIGN: right">1/0/1900</TD><TD style="BACKGROUND-COLOR: #eaf1f6; TEXT-ALIGN: right">

</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD>8</TD><TD>21867</TD><TD>22864</TD><TD>400889</TD><TD style="BORDER-RIGHT: black 1px solid">1</TD><TD style="BORDER-LEFT: black 1px solid">11/4/2010</TD><TD>12/7/2010</TD><TD style="TEXT-ALIGN: right">493.00</TD><TD style="TEXT-ALIGN: right">1/0/1900</TD><TD style="BACKGROUND-COLOR: #eaf1f6; TEXT-ALIGN: right">

</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD>8</TD><TD>21867</TD><TD>22864</TD><TD>400889</TD><TD style="BORDER-RIGHT: black 1px solid">1</TD><TD style="BORDER-LEFT: black 1px solid">12/8/2010</TD><TD>12/19/2010</TD><TD style="TEXT-ALIGN: right">406.00</TD><TD style="TEXT-ALIGN: right">1/0/1900</TD><TD style="BACKGROUND-COLOR: #eaf1f6; TEXT-ALIGN: right">

</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">11</TD><TD style="BACKGROUND-COLOR: #ffff00">8</TD><TD style="BACKGROUND-COLOR: #ffff00">21867</TD><TD style="BACKGROUND-COLOR: #ffff00">22864</TD><TD style="BACKGROUND-COLOR: #ffff00">400889</TD><TD style="BORDER-RIGHT: black 1px solid; BACKGROUND-COLOR: #ffff00">1</TD><TD style="BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00">12/20/2010</TD><TD style="BACKGROUND-COLOR: #ffff00">3/31/2011</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">406.00</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">1/0/1900</TD><TD style="BACKGROUND-COLOR: #ffff00">WHY 0?</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">12</TD><TD>8</TD><TD>21867</TD><TD>22864</TD><TD>400889</TD><TD style="BORDER-RIGHT: black 1px solid">1</TD><TD style="BORDER-LEFT: black 1px solid">4/1/2011</TD><TD>4/14/2011</TD><TD style="TEXT-ALIGN: right">418.00</TD><TD style="TEXT-ALIGN: right">1/0/1900</TD><TD style="BACKGROUND-COLOR: #eaf1f6; TEXT-ALIGN: right">

</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">13</TD><TD>8</TD><TD>21867</TD><TD>22864</TD><TD>400889</TD><TD style="BORDER-RIGHT: black 1px solid">1</TD><TD style="BORDER-LEFT: black 1px solid">4/15/2011</TD><TD>4/30/2011</TD><TD style="TEXT-ALIGN: right">426.00</TD><TD style="TEXT-ALIGN: right">1/0/1900</TD><TD style="BACKGROUND-COLOR: #eaf1f6; TEXT-ALIGN: right">

</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">14</TD><TD style="BORDER-BOTTOM: black 1px solid">8</TD><TD style="BORDER-BOTTOM: black 1px solid">21867</TD><TD style="BORDER-BOTTOM: black 1px solid">22864</TD><TD style="BORDER-BOTTOM: black 1px solid">400889</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-BOTTOM: black 1px solid">1</TD><TD style="BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid">5/1/2011</TD><TD style="BORDER-BOTTOM: black 1px solid">12/31/9999</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">450.00</TD><TD style="TEXT-ALIGN: right">1/0/1900</TD><TD style="BACKGROUND-COLOR: #eaf1f6; TEXT-ALIGN: right">

</TD></TR></TBODY></TABLE>Sheet1

I imagine that if I could get this formula to return the correct date instead of zero then I could probably use it in the SUMPRODUCT formula as you had suggested.

Here are the results I get using this array formula entered in X3 then copied down:

=MAX(IF(($V$3:$V$14) < DATE(2011,4,1)*($S$3:$S$14=S3)*($P$3:$P$14=P3)*($R$3:$R$14=R3)*($T$3:$T$14=T3),$V$3:$V$14))

Book1

X

3

1/23/2011

4

1/23/2011

5

1/23/2011

6

1/23/2011

7

1/23/2011

8

3/31/2011

9

3/31/2011

10

3/31/2011

11

3/31/2011

12

3/31/2011

13

3/31/2011

14

3/31/2011

Sheet1

</date(2011,4,1)*($s$3:$s$14=s4)*($p$3:$p$14=p4)*($r$3:$r$14=r4)*($t$3:$t$14=2),$v$3:$v$14))<>

Hopeless Cub Fan · May 26, 2011

And I overlook having the Incoterms reference as a static value of "2". Of course anything that has a different value will return 0.

I got the same results once I changed it to a cell reference. I'll give this a try with the SUMPRODUCT formula and let you know how it goes. Thanks a ton for your help!

Hopeless Cub Fan · May 26, 2011

Formula below enter as an array appears to work.

=SUMPRODUCT(--($G$2:$G$2687=$G2),--($J$2:$J$2687=$J2),--($L$2:$L$2687=$L2),--($O$2:$O$2687=O2),--($R$2:$R$2687=$AD2), $S$2:$S$2687)

Cell $AD2 in the formula above contains the array formula below.

=MAX(IF(($R$2:$R$2687) < DATE(2011,4,1)*($G$2:$G$2687=$G2)*($J$2:$J$2687=$J2)*($L$2:$L$2687=$L2)*($O$2:$O$2687=$O2),$R$2:$R$2687))

The combined array formula below also works. It is just a combination of the two separate formulas. I need to hit CTRL + SHIFT + ENTER to make it work. I prefer it to be broken out into the separate cells like above so that if I ever have to remember what I did it is easier to look at it separated out.

=SUMPRODUCT(--($G$2:$G$2687=$G68),--($J$2:$J$2687=$J68),--($L$2:$L$2687=$L68),--($O$2:$O$2687=2),--($R$2:$R$2687=MAX(IF(($R$2:$R$2687 < DATE(2011,4,1)*($G$2:$G$2687=$G68)*($J$2:$J$2687=$J68)*($L$2:$L$2687=$L68)*($O$2:$O$2687=$O68),$R$2:$R$2687))),$S$2:$S$2687)

Thanks again for your help Valko. It is much appreciated!

T. Valko · May 26, 2011

Hopeless Cub Fan said:
Formula below enter as an array appears to work.

=SUMPRODUCT(--($G$2:$G$2687=$G2),--($J$2:$J$2687=$J2),--($L$2:$L$2687=$L2),--($O$2:$O$2687=O2),--($R$2:$R$2687=$AD2), $S$2:$S$2687)

Cell $AD2 in the formula above contains the array formula below.

=MAX(IF(($R$2:$R$2687) < DATE(2011,4,1)*($G$2:$G$2687=$G2)*($J$2:$J$2687=$J2)*($L$2:$L$2687=$L2)*($O$2:$O$2687=$O2),$R$2:$R$2687))

The combined array formula below also works. It is just a combination of the two separate formulas. I need to hit CTRL + SHIFT + ENTER to make it work. I prefer it to be broken out into the separate cells like above so that if I ever have to remember what I did it is easier to look at it separated out.

=SUMPRODUCT(--($G$2:$G$2687=$G68),--($J$2:$J$2687=$J68),--($L$2:$L$2687=$L68),--($O$2:$O$2687=2),--($R$2:$R$2687=MAX(IF(($R$2:$R$2687 < DATE(2011,4,1)*($G$2:$G$2687=$G68)*($J$2:$J$2687=$J68)*($L$2:$L$2687=$L68)*($O$2:$O$2687=$O68),$R$2:$R$2687))),$S$2:$S$2687)

Thanks again for your help Valko. It is much appreciated!

I knew the combined formula would be pretty long so that's why I suggested using the intermediate cell(s) to get the max date.

Glad it worked out for you. Thanks for the feedback!

SUMPRODUCT with MAX Date

Hopeless Cub Fan

New Member

Excel Facts

T. Valko

Well-known Member

Hopeless Cub Fan

New Member

T. Valko

Well-known Member

Hopeless Cub Fan

New Member

Hopeless Cub Fan

New Member

T. Valko

Well-known Member

Similar threads

Forum statistics

Share this page

SUMPRODUCT with MAX Date

Hopeless Cub Fan

New Member

Excel Facts

T. Valko

Well-known Member

Hopeless Cub Fan

New Member

T. Valko

Well-known Member

Hopeless Cub Fan

New Member

Hopeless Cub Fan

New Member

T. Valko

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock