Sumproduct with dates

[zxchan]

Dear all,

Need your kind help here..
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))

 

[zxchan]

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

Need your kind help here..
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]

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.

=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:

=SUMPRODUCT(--(B5:B1000="PIP"),--(I5:I1000>=Summary!C71),--(I5:I1000<Summary!D71),S5:S1000)

Edit: Try posting your formula within PHP tags

 

[Stormseed]

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))))<>

 

[Stormseed]

I am trying this again with the PHP tags..

Confirm with CTRL+SHIFT+ENTER:

=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