SUMAR.SI por el mes de una fecha ???

luis romero · May 30, 2002

Buen dia amigos,

En la columna A tengo fechas y en la B tengo importes. en otro lado tengo meses. como podria hacerle para que me sume importes por cada mes respectivo?, he intentado la sig formula pero no me funciona:

=SUMAR.SI(fechas,mes(fechas)=1,importes)

fechas es el rango de las fechas de movimiento, Importes es el rango a sumar

De antemano, gracias por sus aportaciones.

Luis Romero

Renzo G · May 30, 2002

Luis,

Lo que puedes hacer es crear una columna extra "C" donde colocas la formula mes (que te devolvera un numero del 1 al doce), a continuacion en la cuarta columna colocas verticalmente una numeracion del 1 al 12. la formula que deberias usar seria la siguiente:

SUMAR.SI($C$2:$C$30,D$1,$B$2:$B$21)

Saludos,

Renzo

Juan Pablo González · May 30, 2002

Algo así sirve ?

Libro1

A

B

C

D

E

1

Fecha

Importe

Mes

Importe

2

27 sep 02

$ 1,097

1

$ 0

3

11 jun 02

$ 1,096

2

$ 0

4

07 nov 02

$ 328

3

$ 0

5

10 sep 02

$ 189

4

$ 0

6

04 jun 02

$ 890

5

$ 0

7

19 nov 02

$ 179

6

$ 4,462

8

06 jun 02

$ 575

7

$ 1,219

9

05 jun 02

$ 616

8

$ 1,754

10

07 dic 02

$ 519

9

$ 1,286

11

19 oct 02

$ 808

10

$ 3,377

12

18 oct 02

$ 673

11

$ 1,866

13

24 oct 02

$ 1,037

12

$ 519

14

03 jun 02

$ 519

15

18 nov 02

$ 677

16

04 jul 02

$ 986

17

16 jun 02

$ 766

18

10 nov 02

$ 682

19

04 oct 02

$ 501

20

08 oct 02

$ 358

21

04 jul 02

$ 233

22

18 ago 02

$ 1,037

23

26 ago 02

$ 717

...

_________________
Regards,

Juan Pablo G.
MrExcel.com Consulting
This message was edited by Juan Pablo G. on 2002-05-30 15:46

luis romero · May 31, 2002

Renzo, Juan Pablo

Gracias por su ayuda, me quedo muy claro como resolver mi problema. ambas opciones funcionan perfectamente.

Saludos cordiales,
Luis Romero

Renzo G · May 31, 2002

Juan Pablo,

Como haces para insertar esa hoja de Excel creo que asi es mas rapido que puedas comprender las preguntas.
Donde puedo encontrar ese programa?

Saludos,

Renzo

Juan Pablo González · May 31, 2002

En este vínculo que aparece abajo de la página.

Download Colo's cool utility for displaying your Excel Worksheet on the board.

mapavi · Mar 15, 2010

Quisiera agregar un matiz adicional, que pasa cuando cambiamos de años. Como se resolvería este problema, porque me viene barbaro, pero tengo el problema que la serie es de 7 años.

Muchas Gracias

Greg Truby · Mar 30, 2010

Mapavi,

Usando el ejemplo provisto por JPG, uno podría hacer algo como:

<TABLE border=1 cellSpacing=0 borderColor=#c0c0c0 borderColorDark=#ffffff><TBODY><TR><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=160 align=middle>Fecha</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=middle>Importe</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63>Año</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=middle>Mes</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=middle>Importe</TD></TR><TR><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=160 align=right>27-Sep-02</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right>$1,097</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffff00 height=25 vAlign=bottom width=63 align=right>2002</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffff00 height=25 vAlign=bottom width=63 align=right>6</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffff00 height=25 vAlign=bottom width=63 align=right>$4,462</TD></TR><TR><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=160 align=right>11-Jun-02</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right>$1,096</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffff00 height=25 vAlign=bottom width=63 align=right>2002</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffff00 height=25 vAlign=bottom width=63 align=right>7</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffff00 height=25 vAlign=bottom width=63 align=right>$1,219</TD></TR><TR><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=160 align=right>7-Nov-02</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right>$328</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffff00 height=25 vAlign=bottom width=63 align=right>2002</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffff00 height=25 vAlign=bottom width=63 align=right>8</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffff00 height=25 vAlign=bottom width=63 align=right>$1,754</TD></TR><TR><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=160 align=right>10-Sep-02</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right>$189</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffff00 height=25 vAlign=bottom width=63 align=right>2002</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffff00 height=25 vAlign=bottom width=63 align=right>9</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffff00 height=25 vAlign=bottom width=63 align=right>$1,286</TD></TR><TR><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=160 align=right>4-Jun-02</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right>$890</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffff00 height=25 vAlign=bottom width=63 align=right>2002</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffff00 height=25 vAlign=bottom width=63 align=right>10</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffff00 height=25 vAlign=bottom width=63 align=right>$3,377</TD></TR><TR><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=160 align=right>19-Nov-02</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right>$179</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffff00 height=25 vAlign=bottom width=63 align=right>2002</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffff00 height=25 vAlign=bottom width=63 align=right>11</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffff00 height=25 vAlign=bottom width=63 align=right>$1,866</TD></TR><TR><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=160 align=right>6-Jun-02</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right>$575</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffff00 height=25 vAlign=bottom width=63 align=right>2002</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffff00 height=25 vAlign=bottom width=63 align=right>12</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffff00 height=25 vAlign=bottom width=63 align=right>$519</TD></TR><TR><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=160 align=right>5-Jun-02</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right>$616</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>2003</TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>1</TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>-</TD></TR><TR><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=160 align=right>12/7/2002</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right>$519</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>2003</TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>2</TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>$987</TD></TR><TR><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=160 align=right>19-Oct-02</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right>$808</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>2003</TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>3</TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>$3,446</TD></TR><TR><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=160 align=right>18-Oct-02</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right>$673</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>2003</TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>4</TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>$2,070</TD></TR><TR><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=160 align=right>24-Oct-02</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right>$1,037</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>2003</TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>5</TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>-</TD></TR><TR><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=160 align=right>3-Jun-02</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right>$519</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>2003</TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>6</TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>$812</TD></TR><TR><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=160 align=right>18-Nov-02</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right>$677</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>2003</TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>7</TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>-</TD></TR><TR><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=160 align=right>4-Jul-02</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right>$986</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>2003</TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>8</TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>$635</TD></TR><TR><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=160 align=right>16-Jun-02</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right>$766</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>2003</TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>9</TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>-</TD></TR><TR><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=160 align=right>10-Nov-02</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right>$682</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>2003</TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>10</TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>$1,334</TD></TR><TR><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=160 align=right>4-Oct-02</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right>$501</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>2003</TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>11</TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>-</TD></TR><TR><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=160 align=right>8-Oct-02</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right>$358</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>2003</TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>12</TD><TD style="WHITE-SPACE: nowrap" bgColor=#99cc00 height=25 vAlign=bottom width=63 align=right>$2,904</TD></TR><TR><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=160 align=right>4-Jul-02</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right>$233</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" bgColor=#00ccff height=25 vAlign=bottom width=63 align=right>2004</TD><TD style="WHITE-SPACE: nowrap" bgColor=#00ccff height=25 vAlign=bottom width=63 align=right>1</TD><TD style="WHITE-SPACE: nowrap" bgColor=#00ccff height=25 vAlign=bottom width=63 align=right>$1,659</TD></TR><TR><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=160 align=right>8/18/2002</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right>$1,037</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" bgColor=#00ccff height=25 vAlign=bottom width=63 align=right>2004</TD><TD style="WHITE-SPACE: nowrap" bgColor=#00ccff height=25 vAlign=bottom width=63 align=right>2</TD><TD style="WHITE-SPACE: nowrap" bgColor=#00ccff height=25 vAlign=bottom width=63 align=right>-</TD></TR><TR><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=160 align=right>8/26/2002</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right>$717</TD><TD style="WHITE-SPACE: nowrap" bgColor=#ffffff height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" bgColor=#00ccff height=25 vAlign=bottom width=63 align=right>2004</TD><TD style="WHITE-SPACE: nowrap" bgColor=#00ccff height=25 vAlign=bottom width=63 align=right>3</TD><TD style="WHITE-SPACE: nowrap" bgColor=#00ccff height=25 vAlign=bottom width=63 align=right>$1,913</TD></TR><TR><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=160>28-Jun-04</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right>$213</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" bgColor=#00ccff height=25 vAlign=bottom width=63 align=right>2004</TD><TD style="WHITE-SPACE: nowrap" bgColor=#00ccff height=25 vAlign=bottom width=63 align=right>4</TD><TD style="WHITE-SPACE: nowrap" bgColor=#00ccff height=25 vAlign=bottom width=63 align=right>$2,229</TD></TR><TR><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=160>28-Oct-04</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right>$383</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" bgColor=#00ccff height=25 vAlign=bottom width=63 align=right>2004</TD><TD style="WHITE-SPACE: nowrap" bgColor=#00ccff height=25 vAlign=bottom width=63 align=right>5</TD><TD style="WHITE-SPACE: nowrap" bgColor=#00ccff height=25 vAlign=bottom width=63 align=right>-</TD></TR><TR><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=160>8-Oct-03</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right>$1,334</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" bgColor=#00ccff height=25 vAlign=bottom width=63 align=right>2004</TD><TD style="WHITE-SPACE: nowrap" bgColor=#00ccff height=25 vAlign=bottom width=63 align=right>6</TD><TD style="WHITE-SPACE: nowrap" bgColor=#00ccff height=25 vAlign=bottom width=63 align=right>$213</TD></TR><TR><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=160>23-Sep-04</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right>$1,282</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD></TR><TR><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=160>24-Dec-03</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right>$1,482</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD></TR><TR><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=160>1-Mar-04</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right>$1,913</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD></TR><TR><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=160>21-Dec-04</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right>$1,486</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD></TR><TR><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=160>3-Dec-03</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right>$930</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD></TR><TR><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=160>28-Aug-04</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right>$1,295</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD></TR><TR><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=160>23-Sep-04</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right>$1,946</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD></TR><TR><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=160>9-Aug-03</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right>$635</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD></TR><TR><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=160>17-Jul-04</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right>$1,034</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD></TR><TR><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=160>3-Apr-04</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right>$247</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD></TR><TR><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=160>19-Jan-04</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right>$1,659</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD></TR><TR><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=160>28-Dec-04</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right>$322</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD></TR><TR><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=160>16-Mar-03</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right>$1,870</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD></TR><TR><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=160>9-Feb-03</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right>$987</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD></TR><TR><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=160>15-Oct-04</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right>$1,322</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD></TR><TR><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=160>13-Apr-03</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right>$1,905</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD></TR><TR><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=160>25-Apr-04</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right>$1,035</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD></TR><TR><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=160>4-Dec-03</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right>$492</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD></TR><TR><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=160>8-Apr-04</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right>$947</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD></TR><TR><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=160>18-Jun-03</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right>$812</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD></TR><TR><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=160>30-Apr-03</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right>$165</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD></TR><TR><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=160>11-Jul-04</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right>$184</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD></TR><TR><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=160>19-Mar-03</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right>$1,576</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD></TR><TR><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=160>22-Oct-04</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right>$282</TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD><TD style="WHITE-SPACE: nowrap" height=25 vAlign=bottom width=63 align=right></TD></TR></TBODY></TABLE>

Greg Truby · Mar 30, 2010

Aquí está otra vez, pero con las fórmulas. Pero tengo que decirle que para solucionar una situación así, usaría una Pivot Table (tabla dinámica) y no fórmulas.

<table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellspacing="0"><tr><td bgcolor="#FFFFFF" width="103" height="25" align="center" valign="bottom" style="white-space: nowrap">Fecha</td><td bgcolor="#FFFFFF" width="63" height="25" align="center" valign="bottom" style="white-space: nowrap">Importe</td><td bgcolor="#FFFFFF" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap"></td><td bgcolor="#FFFFFF" width="63" height="25" valign="bottom" style="white-space: nowrap">Año</td><td bgcolor="#FFFFFF" width="63" height="25" align="center" valign="bottom" style="white-space: nowrap">Mes</td><td bgcolor="#FFFFFF" width="294" height="25" align="center" valign="bottom" style="white-space: nowrap">Importe</td></tr><tr><td bgcolor="#FFFFFF" width="103" height="25" align="right" valign="bottom" style="white-space: nowrap">37526</td><td bgcolor="#FFFFFF" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">1097</td><td bgcolor="#FFFFFF" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap"></td><td bgcolor="#FFFF00" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">2002</td><td bgcolor="#FFFF00" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">6</td><td bgcolor="#FFFF00" width="294" height="25" align="right" valign="bottom" style="white-space: nowrap">=SUMPRODUCT((MONTH(A$2:$A$50)=E2)*(YEAR(A$2:A$50)=D2)*$B$2:$B$50)</td></tr><tr><td bgcolor="#FFFFFF" width="103" height="25" align="right" valign="bottom" style="white-space: nowrap">37418</td><td bgcolor="#FFFFFF" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">1096</td><td bgcolor="#FFFFFF" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap"></td><td bgcolor="#FFFF00" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">2002</td><td bgcolor="#FFFF00" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">7</td><td bgcolor="#FFFF00" width="294" height="25" align="right" valign="bottom" style="white-space: nowrap">=SUMPRODUCT((MONTH(A$2:$A$50)=E3)*(YEAR(A$2:A$50)=D3)*$B$2:$B$50)</td></tr><tr><td bgcolor="#FFFFFF" width="103" height="25" align="right" valign="bottom" style="white-space: nowrap">37567</td><td bgcolor="#FFFFFF" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">328</td><td bgcolor="#FFFFFF" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap"></td><td bgcolor="#FFFF00" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">2002</td><td bgcolor="#FFFF00" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">8</td><td bgcolor="#FFFF00" width="294" height="25" align="right" valign="bottom" style="white-space: nowrap">=SUMPRODUCT((MONTH(A$2:$A$50)=E4)*(YEAR(A$2:A$50)=D4)*$B$2:$B$50)</td></tr><tr><td bgcolor="#FFFFFF" width="103" height="25" align="right" valign="bottom" style="white-space: nowrap">37509</td><td bgcolor="#FFFFFF" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">189</td><td bgcolor="#FFFFFF" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap"></td><td bgcolor="#FFFF00" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">2002</td><td bgcolor="#FFFF00" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">9</td><td bgcolor="#FFFF00" width="294" height="25" align="right" valign="bottom" style="white-space: nowrap">=SUMPRODUCT((MONTH(A$2:$A$50)=E5)*(YEAR(A$2:A$50)=D5)*$B$2:$B$50)</td></tr><tr><td bgcolor="#FFFFFF" width="103" height="25" align="right" valign="bottom" style="white-space: nowrap">37411</td><td bgcolor="#FFFFFF" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">890</td><td bgcolor="#FFFFFF" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap"></td><td bgcolor="#FFFF00" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">2002</td><td bgcolor="#FFFF00" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">10</td><td bgcolor="#FFFF00" width="294" height="25" align="right" valign="bottom" style="white-space: nowrap">=SUMPRODUCT((MONTH(A$2:$A$50)=E6)*(YEAR(A$2:A$50)=D6)*$B$2:$B$50)</td></tr><tr><td bgcolor="#FFFFFF" width="103" height="25" align="right" valign="bottom" style="white-space: nowrap">37579</td><td bgcolor="#FFFFFF" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">179</td><td bgcolor="#FFFFFF" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap"></td><td bgcolor="#FFFF00" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">2002</td><td bgcolor="#FFFF00" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">11</td><td bgcolor="#FFFF00" width="294" height="25" align="right" valign="bottom" style="white-space: nowrap">=SUMPRODUCT((MONTH(A$2:$A$50)=E7)*(YEAR(A$2:A$50)=D7)*$B$2:$B$50)</td></tr><tr><td bgcolor="#FFFFFF" width="103" height="25" align="right" valign="bottom" style="white-space: nowrap">37413</td><td bgcolor="#FFFFFF" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">575</td><td bgcolor="#FFFFFF" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap"></td><td bgcolor="#FFFF00" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">2002</td><td bgcolor="#FFFF00" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">12</td><td bgcolor="#FFFF00" width="294" height="25" align="right" valign="bottom" style="white-space: nowrap">=SUMPRODUCT((MONTH(A$2:$A$50)=E8)*(YEAR(A$2:A$50)=D8)*$B$2:$B$50)</td></tr><tr><td bgcolor="#FFFFFF" width="103" height="25" align="right" valign="bottom" style="white-space: nowrap">37412</td><td bgcolor="#FFFFFF" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">616</td><td bgcolor="#FFFFFF" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap"></td><td bgcolor="#99CC00" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">2003</td><td bgcolor="#99CC00" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">1</td><td bgcolor="#99CC00" width="294" height="25" align="right" valign="bottom" style="white-space: nowrap">=SUMPRODUCT((MONTH(A$2:$A$50)=E9)*(YEAR(A$2:A$50)=D9)*$B$2:$B$50)</td></tr><tr><td bgcolor="#FFFFFF" width="103" height="25" align="right" valign="bottom" style="white-space: nowrap">37597</td><td bgcolor="#FFFFFF" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">519</td><td bgcolor="#FFFFFF" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap"></td><td bgcolor="#99CC00" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">2003</td><td bgcolor="#99CC00" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">2</td><td bgcolor="#99CC00" width="294" height="25" align="right" valign="bottom" style="white-space: nowrap">=SUMPRODUCT((MONTH(A$2:$A$50)=E10)*(YEAR(A$2:A$50)=D10)*$B$2:$B$50)</td></tr><tr><td bgcolor="#FFFFFF" width="103" height="25" align="right" valign="bottom" style="white-space: nowrap">37548</td><td bgcolor="#FFFFFF" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">808</td><td bgcolor="#FFFFFF" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap"></td><td bgcolor="#99CC00" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">2003</td><td bgcolor="#99CC00" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">3</td><td bgcolor="#99CC00" width="294" height="25" align="right" valign="bottom" style="white-space: nowrap">=SUMPRODUCT((MONTH(A$2:$A$50)=E11)*(YEAR(A$2:A$50)=D11)*$B$2:$B$50)</td></tr><tr><td bgcolor="#FFFFFF" width="103" height="25" align="right" valign="bottom" style="white-space: nowrap">37547</td><td bgcolor="#FFFFFF" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">673</td><td bgcolor="#FFFFFF" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap"></td><td bgcolor="#99CC00" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">2003</td><td bgcolor="#99CC00" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">4</td><td bgcolor="#99CC00" width="294" height="25" align="right" valign="bottom" style="white-space: nowrap">=SUMPRODUCT((MONTH(A$2:$A$50)=E12)*(YEAR(A$2:A$50)=D12)*$B$2:$B$50)</td></tr><tr><td bgcolor="#FFFFFF" width="103" height="25" align="right" valign="bottom" style="white-space: nowrap">37553</td><td bgcolor="#FFFFFF" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">1037</td><td bgcolor="#FFFFFF" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap"></td><td bgcolor="#99CC00" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">2003</td><td bgcolor="#99CC00" width="63" height="25" align="right" valign="bottom" style="white-space: nowrap">5</td><td bgcolor="#99CC00" width="294" height="25" align="right" valign="bottom" style="white-space: nowrap">=SUMPRODUCT((MONTH(A$2:$A$50)=E13)*(YEAR(A$2:A$50)=D13)*$B$2:$B$50)</td></tr></table>

Atte,

Diegoprem · Sep 22, 2016

Gracias, me parece genial usar aquí esta función, sólo quisiera saber si me pudieras ayudar para no sólo sumar por el mes, sino también año, asumamos que en el cuadro que enviaste las fechas fueran para una proyección, y quisieras tomar el mes de enero del año 2016, y en otra celda el mes de enero del año 2017.

Muchas gracias,

Diego Prem

Juan Pablo González said:
Algo así sirve ?

<center>

Microsoft Excel - Libro1 ___Running: xl2000 : OS = Windows NT4.0

<tbody>
</tbody>

(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp <form name="formCb117"><input name="btCb339" *******="window.clipboardData.setData("Text",document.formFb202.sltNb942.value);" type="button" value="Copy Formula"></form>

<tbody>
</tbody>

<select name="sltNb942" onchange="document.formFb202.txbFb116.value = document.formFb202.sltNb942.value"><option value="=SUMAPRODUCTO((MES($A$2:$A$23)=D2)*$B$2:$B$23)">E2</option><option value="=SUMAPRODUCTO((MES($A$2:$A$23)=D3)*$B$2:$B$23)">E3</option><option value="=SUMAPRODUCTO((MES($A$2:$A$23)=D4)*$B$2:$B$23)">E4</option><option value="=SUMAPRODUCTO((MES($A$2:$A$23)=D5)*$B$2:$B$23)">E5</option><option value="=SUMAPRODUCTO((MES($A$2:$A$23)=D6)*$B$2:$B$23)">E6</option><option value="=SUMAPRODUCTO((MES($A$2:$A$23)=D7)*$B$2:$B$23)">E7</option><option value="=SUMAPRODUCTO((MES($A$2:$A$23)=D8)*$B$2:$B$23)">E8</option><option selected="" value="=SUMAPRODUCTO((MES($A$2:$A$23)=D9)*$B$2:$B$23)">E9</option><option value="=SUMAPRODUCTO((MES($A$2:$A$23)=D10)*$B$2:$B$23)">E10</option><option value="=SUMAPRODUCTO((MES($A$2:$A$23)=D11)*$B$2:$B$23)">E11</option><option value="=SUMAPRODUCTO((MES($A$2:$A$23)=D12)*$B$2:$B$23)">E12</option><option value="=SUMAPRODUCTO((MES($A$2:$A$23)=D13)*$B$2:$B$23)">E13</option></select> = <input name="txbFb116" size="120" value="=SUMAPRODUCTO((MES($A$2:$A$23)=D9)*$B$2:$B$23)">

<tbody>
<form name="formFb202"></form>
</tbody>
<center>A</center> <center>B</center> <center>C</center> <center>D</center> <center>E</center>
<center>1</center> Fecha Importe Mes Importe
<center>2</center> 27 sep 02 $ 1,097 1 $ 0
<center>3</center> 11 jun 02 $ 1,096 2 $ 0
<center>4</center> 07 nov 02 $ 328 3 $ 0
<center>5</center> 10 sep 02 $ 189 4 $ 0
<center>6</center> 04 jun 02 $ 890 5 $ 0
<center>7</center> 19 nov 02 $ 179 6 $ 4,462
<center>8</center> 06 jun 02 $ 575 7 $ 1,219
<center>9</center> 05 jun 02 $ 616 8 $ 1,754
<center>10</center> 07 dic 02 $ 519 9 $ 1,286
<center>11</center> 19 oct 02 $ 808 10 $ 3,377
<center>12</center> 18 oct 02 $ 673 11 $ 1,866
<center>13</center> 24 oct 02 $ 1,037 12 $ 519
<center>14</center> 03 jun 02 $ 519
<center>15</center> 18 nov 02 $ 677
<center>16</center> 04 jul 02 $ 986
<center>17</center> 16 jun 02 $ 766
<center>18</center> 10 nov 02 $ 682
<center>19</center> 04 oct 02 $ 501
<center>20</center> 08 oct 02 $ 358
<center>21</center> 04 jul 02 $ 233
<center>22</center> 18 ago 02 $ 1,037
<center>23</center> 26 ago 02 $ 717
Hoja1

<tbody>
</tbody>

To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The above image was automatically generated by [HtmlMaker 2.10] If you want this FREE SOFT, click here to download
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo
</center>
_________________
Regards,

Juan Pablo G.
MrExcel.com Consulting

[SIZE=-1][ This Message was edited by: Juan Pablo G. on 2002-05-30 15:46 ][/SIZE]

SUMAR.SI por el mes de una fecha ???

luis romero

New Member

Excel Facts

Renzo G

New Member

Juan Pablo González

MrExcel MVP

luis romero

New Member

Renzo G

New Member

Juan Pablo González

MrExcel MVP

mapavi

New Member

Greg Truby

MrExcel MVP

Greg Truby

MrExcel MVP

Diegoprem

New Member

Similar threads

Forum statistics

Share this page

SUMAR.SI por el mes de una fecha ???

New Member

Excel Facts

New Member

MrExcel MVP

New Member

New Member

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

New 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