Range formula to find values between dates

[el_ja]

Hello once again,
I have this sheet where I have some values like this :
<table width="358" border="0" cellpadding="0" cellspacing="0"><col style="mso-width-source:userset;mso-width-alt:1389;width:29pt" width="38"> <col style="width:60pt" width="80" span="4"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl76" style="height:15.0pt;width:29pt" width="38" height="20">Code</td> <td class="xl76" style="width:60pt" width="80">Date</td> <td class="xl75" style="width:60pt" width="80" align="center">A</td> <td class="xl75" style="width:60pt" width="80" align="center">B</td> <td class="xl75" style="width:60pt" width="80" align="center">C</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl73" style="height:15.0pt" height="20">AM</td> <td class="xl73">10-Feb-10</td> <td class="xl74" align="right">(50,00)</td> <td class="xl78">
</td> <td class="xl78">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl73" style="height:15.0pt" height="20">AM</td> <td class="xl73">30-Apr-10</td> <td class="xl74" align="right">(73,44)</td> <td class="xl74">
</td> <td class="xl74">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl73" style="height:15.0pt" height="20">AM</td> <td class="xl73">25-May-10</td> <td class="xl77">
</td> <td class="xl74">
</td> <td class="xl74" align="right">(180,00)</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl73" style="height:15.0pt" height="20">AM</td> <td class="xl73">11-Jun-10</td> <td class="xl74" align="right">(280,00)</td> <td class="xl74">
</td> <td class="xl74">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl73" style="height:15.0pt" height="20">AM</td> <td class="xl73">29-Jun-10</td> <td class="xl74" align="right">(50,00)</td> <td class="xl74">
</td> <td class="xl74">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl73" style="height:15.0pt" height="20">AM</td> <td class="xl73">2-Dec-10</td> <td class="xl77">
</td> <td class="xl74" align="right">(60,00)</td> <td class="xl74">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl73" style="height:15.0pt" height="20">AM</td> <td class="xl73">21-Feb-11</td> <td class="xl74" align="right">(500,00)</td> <td class="xl74">
</td> <td class="xl74">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl73" style="height:15.0pt" height="20">AM</td> <td class="xl73">2-Mar-11</td> <td class="xl74" align="right">(9,25)</td> <td class="xl74">
</td> <td class="xl74">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl73" style="height:15.0pt" height="20">AM</td> <td class="xl73">31-Mar-11</td> <td class="xl74">
</td> <td class="xl74" align="right">(473,98)</td> <td class="xl74">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl73" style="height:15.0pt" height="20">AM</td> <td class="xl73">18-May-11</td> <td class="xl74" align="right">(250,00)</td> <td class="xl74">
</td> <td class="xl74">
</td> </tr> </tbody></table>
And I want to bring the values by month/year and the code in another sheet like this:

<table width="357" border="0" cellpadding="0" cellspacing="0" height="40"><col style="mso-width-source:userset;mso-width-alt:914;width:19pt" width="25"> <col style="mso-width-source:userset;mso-width-alt:1462;width:30pt" width="40"> <col style="mso-width-source:userset;mso-width-alt:1938;width:40pt" width="53"> <col style="mso-width-source:userset;mso-width-alt:1462;width:30pt" width="40"> <col style="mso-width-source:userset;mso-width-alt:1938; width:40pt" width="53" span="2"> <col style="mso-width-source:userset;mso-width-alt:1462;width:30pt" width="40"> <tbody><tr><td valign="top">
</td><td valign="top" align="center">
</td><td valign="top" align="center">G
</td><td valign="top" align="center">H
</td><td valign="top" align="center">I
</td><td valign="top" align="center">J
</td><td valign="top" align="center">K
</td><td valign="top" align="center">L
</td><td valign="top" align="center">M
</td></tr><tr style="height:15.0pt" height="20"> <td class="xl74" style="height:15.0pt;width:19pt" width="25" height="20">4
</td> <td valign="top">
</td><td valign="top">
</td><td class="xl73" style="width:30pt" width="40" align="right">Jun-11</td> <td class="xl73" style="width:40pt" width="53" align="right">May-11</td> <td class="xl73" style="width:30pt" width="40" align="right">Apr-11</td> <td class="xl73" style="width:40pt" width="53" align="right">Mar-11</td> <td class="xl73" style="width:40pt" width="53" align="right">Feb-11</td> <td class="xl73" style="width:30pt" width="40" align="right">Jan-11</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl74" style="height:15.0pt" height="20">5</td> <td valign="top">
</td><td valign="top">AM
</td><td class="xl74" align="right">0</td> <td class="xl75" align="right">(250,00)</td> <td class="xl74" align="right">0</td> <td class="xl75" align="right">(483,23)</td> <td class="xl75" align="right">(500,00)</td> <td class="xl74" align="right">0</td> </tr> </tbody></table>
That is based on the code AM and the month and the year because as you can see there can be values from the same month but different years.
So I'm using a formula like this:
{=SUM(
IF($A$2:$A$10=$G$5;
IF(AND(MONTH($B$2:$B$10)=MONTH(H4);YEAR($B$2:$B$10)=YEAR(H4));
$C$2:$E$10)))}

But I'm getting 0 as results and sometimes #Value errors.
It works if I do it only by month, but not using AND for both conditions.
Anyone knows what could it be?

 

[asad]

I think you should use , instead of ; in your formula.

Asad

 

[el_ja]

Oh yeah, my excel is on a spanish region configuration so I use ; instead of ,
And I forgot to edit that and I can't find the edit button here.
So pretend is ,

 

[asad]

And how are the numbers formatted in C2:E10? Why do they have ( and ) around them?

Asad

 

[asad]

I tried this formula in B21 to E21 with dates in B20 to E20.

{=SUM(IF(A2:A11=A21,IF(MONTH(B2:B11)=MONTH(C20),IF(YEAR(B2:B11)=YEAR(B20),(C2:E11)))))}

.
It worked for me when I formatted the numbers in C2:E11 as numbers. It gave me the answer 50000.

 

[el_ja]

Sorry for the very late reply, I had internet trouble.
But that worked, leaving out the and and doing 2 ifs.
Thanks

 

[asad]

Thanks for the feedback, this way I know that I was able to solve the problem.

Asad