# Sumproduct with dates

#### zxchan

##### New Member
Dear all,

Summary!C71 is 1-jan-08
Summary!D71 is 1-feb-08

I am trying to sum up all the value that lies in S5:S1000 for all "PIP" in row B with all the dates that is between 1-jan08 and 1-feb-08.

The problem seems to lie in the bold red portion.
Once I remove the red portion, it work.
But I need to remove that to get the correct result

=SUMPRODUCT((B5:B1000="PIP")*(AND(I5:I1000>=Summary!C71,I5:I1000<summary!d71><summary!d71></summary!d71></summary!d71><summary!d71></summary!d71>))*(S5:S1000))

Last edited:

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### zxchan

##### New Member
Dear all,
I am reposting here, as I am not able to correct my changes.

Summary!C71 is 1-jan-08
Summary!D71 is 1-feb-08

I am trying to sum up all the value that lies in S5:S1000 for all "PIP" in row B with all the dates that is between 1-jan08 and 1-feb-08.

The problem seems to lie in the bold red portion.
Once I remove the red portion, it work.
But I need to remove that to get the correct result

=SUMPRODUCT((B5:B1000="PIP")*(AND(I5:I1000>=Summary!C71,I5:I1000<summary!d71><summary!d71></summary!d71></summary!d71><Summary!D71))*(S5:S1000))

#### Jon von der Heyden

##### MrExcel MVP, Moderator
Assuming you only want a total for each month, you could simply house Jan-08 in C71 and the following will provide a total for the month of Jan-08.
PHP:
``=SUMPRODUCT(--(B5:B1000="PIP"),--TEXT(I5:I1000,"mm-yy")=TEXT(Summary!C71,"mm-yy"),S5:S1000)``

Use the following to get a total between two dates:
PHP:
``=SUMPRODUCT(--(B5:B1000="PIP"),--(I5:I1000>=Summary!C71),--(I5:I1000<Summary!D71),S5:S1000)``

Edit: Try posting your formula within PHP tags #### Stormseed

##### Banned
One way would be:

NOTE: Formula should be confirmed with Ctrl+Shift+Enter and not just Enter

<?XML:NAMESPACE PREFIX = Summary!D71,Summary!S5 /><Summary!D71,Summary!S5:S1000))))< b>=SUM(IF(Summary!B5:B1000="PIP",IF(Summary!C5:C1000>Summary!C71,IF(Summary!D5:D1000'<'Summary!D71,Summary!S5:S1000))))</Summary!D71,Summary!S5:S1000))))<>
<Summary!D71,Summary!S5:S1000))))< b></Summary!D71,Summary!S5:S1000))))<>
<Summary!D71,Summary!S5:S1000))))< b>P.S - the formula contains two red inverted quotes after the parameter "IF(Summary!D5:D1000" which should be removed while pasting the formula. The MrExcel webpage is considering the < (less than) symbol as the start of an HTML TAG. </Summary!D71,Summary!S5:S1000))))<>
<Summary!D71,Summary!S5:S1000))))< b><Summary!D71,Summary!S5:S1000))))<Summary!D71,Summary!S5:S1000))))< Summary!D71,Summary!S5:S1000))))<><Summary!D71,Summary!S5:S1000))))< b></Summary!D71,Summary!S5:S1000))))<>
</Summary!D71,Summary!S5:S1000))))<Summary!D71,Summary!S5:S1000))))<></Summary!D71,Summary!S5:S1000))))<>

Last edited:

#### Stormseed

##### Banned
I am trying this again with the PHP tags..

Confirm with CTRL+SHIFT+ENTER:
PHP:
``=SUM(IF(Summary!B5:B1000="PIP",IF(Summary!C5:C1000>Summary!C71,IF(Summary!D5:D1000<Summary!D71,Summary!S5:S1000))))``

Edit: OP can use the above formula Replies
3
Views
192
Replies
1
Views
245
Replies
5
Views
387
Replies
3
Views
346
Replies
2
Views
279

### Forum statistics

1,190,606
Messages
5,981,900
Members
439,743
Latest member
KatieO ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back