SUMPRODUCT with multiple date values

[gary_parker]

Hi

I'm trying to do a formula that produces a sum of all of the products within a specific range.

<table><tbody><tr><td>B </td><td>C</td><td>D</td><td>E</td><td>F</td></tr> <tr><td>  </td><td>03/10/2011</td><td>03/10/2011</td><td>03/10/2011</td><td>03/10/2011</td></tr> <tr><td>  </td><td>Break (P)</td><td>Lunch Unpaid</td><td>Phone</td><td>Retention</td></tr> <tr><td>09:00:00  </td><td>0</td><td>0</td><td>11</td><td>3</td></tr> <tr><td>09:15:00  </td><td>1</td><td>0</td><td>10</td><td>3</td></tr> <tr><td>09:30:00  </td><td>0</td><td>0</td><td>11</td><td>3</td></tr> <tr><td>09:45:00  </td><td>0</td><td>0</td><td>11</td><td>3</td></tr> </tbody></table>

The sumproduct picks up the time (i have added a column that changes the time format (column A)) however, I cannot get the sumproduct to work out that between 9am and 9:45am there is a total of 56, because of the date, it sits at 0....

Can anyone advise? My formula is =SUMPRODUCT(($A$3:$A$46=$B49)*($C$1:$DJ$1=C$48)*$C$3:$DJ$46) where $B49 is the lookup for that particular time range in above mentioned column A and C$48 is the date specified

Gary

 

[Matty]

Try like this:

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:67px;"><col style="width:83px;"><col style="width:83px;"><col style="width:83px;"><col style="width:83px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="font-family:Verdana; ">
</td><td style="font-family:Verdana; text-align:right; ">03/10/2011</td><td style="font-family:Verdana; text-align:right; ">03/10/2011</td><td style="font-family:Verdana; text-align:right; ">03/10/2011</td><td style="font-family:Verdana; text-align:right; ">03/10/2011</td></tr><tr style="height:34px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="font-family:Verdana; ">
</td><td style="font-family:Verdana; ">Break (P)</td><td style="font-family:Verdana; ">Lunch Unpaid</td><td style="font-family:Verdana; ">Phone</td><td style="font-family:Verdana; ">Retention</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="font-family:Verdana; text-align:right; ">09:00:00</td><td style="font-family:Verdana; text-align:right; ">0</td><td style="font-family:Verdana; text-align:right; ">0</td><td style="font-family:Verdana; text-align:right; ">11</td><td style="font-family:Verdana; text-align:right; ">3</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="font-family:Verdana; text-align:right; ">09:15:00</td><td style="font-family:Verdana; text-align:right; ">1</td><td style="font-family:Verdana; text-align:right; ">0</td><td style="font-family:Verdana; text-align:right; ">10</td><td style="font-family:Verdana; text-align:right; ">3</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="font-family:Verdana; text-align:right; ">09:30:00</td><td style="font-family:Verdana; text-align:right; ">0</td><td style="font-family:Verdana; text-align:right; ">0</td><td style="font-family:Verdana; text-align:right; ">11</td><td style="font-family:Verdana; text-align:right; ">3</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style="font-family:Verdana; text-align:right; ">09:45:00</td><td style="font-family:Verdana; text-align:right; ">0</td><td style="font-family:Verdana; text-align:right; ">0</td><td style="font-family:Verdana; text-align:right; ">11</td><td style="font-family:Verdana; text-align:right; ">3</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td style="font-family:Verdana; text-align:right; ">09:00:00</td><td style="font-family:Verdana; text-align:right; ">09:45:00</td><td style="font-family:Verdana; text-align:right; ">03/10/2011</td><td style="text-align:right; ">56</td><td>
</td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>D8</td><td>{=SUM(IF(A3:A6>=A8,IF(A3:A6<=B8,IF(B1:E1=C8,B3:E6))))}</td></tr></tbody></table></td></tr><tr><td>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</td></tr></tbody></table>
Matty

 

[Matty]

Or if you want to stick with SUMPRODUCT:

=SUMPRODUCT((A3:A6>=A8)*(A3:A6<=B8)*(B1:E1=C8),B3:E6)

Matty