sum rows in range change column

[mophsus]

Hi all,

I´m trying to sum some rows with the same criteria in column "A", the criteria that changes, changes by month, i mean this month use march, next april and so on the criteria changes in the report, obviously the report is in another sheet

Cód. Aux./Dpto.	Balances AIFRS (AUD) January	Balances AIFRS (AUD) February	Balances AIFRS (AUD) March	Balances AIFRS (AUD) April
552300	41,650.16	40,436.41	110,196.15	114,788.05
552315	-1,387,469.69	-1,387,469.69	-1,387,469.69	-1,387,469.69
552301	-4,321,908.98	-4,321,908.98	-4,321,908.98	-4,321,908.98
552301	-2,789,625.35	-2,789,625.35	-2,789,625.35	-2,789,625.35
552301	-41,650.16	-40,436.41	-41,613.89	-43,347.95
552301	0	0	-68,582.26	-71,440.10
552302	0	0	0	0
552303	-2,789,625.35	-2,789,625.35	-2,789,625.35	-2,789,625.35
552304	-2,789,625.35	-2,789,625.35	-2,789,625.35	-2,789,625.35

<tbody>
</tbody>

and in my report i change the month

	Balances AIFRS (AUD) March
552301

<tbody>
</tbody>

Next month it will be:

	Balances AIFRS (AUD) April
552301

<tbody>
</tbody>

i tried with sumifs and/or index match in sinple & matrix form but it does not work, any idea? please

Thanks in advance

 

[kweaver]

Here's ONE way...I'm sure there are others.

If your data (with headers) is in A1:E10 (or an extended version of that),

Form a cell to show only the month name when you enter, for example, 3/1/2019 for March.
For a test, I put 552301 in H3 and used the following formula in H4. B2:E10 represents all of the data rows by the month columns (could be 12 months, then that would be to M).

=SUMPRODUCT((A2:A10=H3)*(INDEX(B2:E10,,MONTH(H2))))

and it returned -7,221,730.48

 

[DanteAmor]

Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:106.46px;" /><col style="width:188.2px;" /><col style="width:200.55px;" /><col style="width:178.69px;" /><col style="width:182.5px;" /></colgroup><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:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Cód. Aux./Dpto.</td><td >Balances AIFRS (AUD) January</td><td >Balances AIFRS (AUD) February</td><td >Balances AIFRS (AUD) March</td><td >Balances AIFRS (AUD) April</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">552300</td><td style="text-align:right; ">41,650.16</td><td style="text-align:right; ">40,436.41</td><td style="text-align:right; ">110,196.15</td><td style="text-align:right; ">114,788.05</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">552315</td><td style="text-align:right; ">-1,387,469.69</td><td style="text-align:right; ">-1,387,469.69</td><td style="text-align:right; ">-1,387,469.69</td><td style="text-align:right; ">-1,387,469.69</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">552301</td><td style="text-align:right; ">-4,321,908.98</td><td style="text-align:right; ">-4,321,908.98</td><td style="text-align:right; ">-4,321,908.98</td><td style="text-align:right; ">-4,321,908.98</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">552301</td><td style="text-align:right; ">-2,789,625.35</td><td style="text-align:right; ">-2,789,625.35</td><td style="text-align:right; ">-2,789,625.35</td><td style="text-align:right; ">-2,789,625.35</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">552301</td><td style="text-align:right; ">-41,650.16</td><td style="text-align:right; ">-40,436.41</td><td style="text-align:right; ">-41,613.89</td><td style="text-align:right; ">-43,347.95</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">552301</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">-68,582.26</td><td style="text-align:right; ">-71,440.10</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">552302</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">552303</td><td style="text-align:right; ">-2,789,625.35</td><td style="text-align:right; ">-2,789,625.35</td><td style="text-align:right; ">-2,789,625.35</td><td style="text-align:right; ">-2,789,625.35</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">552304</td><td style="text-align:right; ">-2,789,625.35</td><td style="text-align:right; ">-2,789,625.35</td><td style="text-align:right; ">-2,789,625.35</td><td style="text-align:right; ">-2,789,625.35</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td >Balances AIFRS (AUD) March</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">552301</td><td style="text-align:right; ">-7,221,730.48</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B16</td><td >=SUMIF($A$2:$A$10,A16,INDEX($B$2:$E$10,0,MATCH(B15,$B$1:$E$1,0)))</td></tr></table></td></tr></table>

 

[kweaver]

Book1
	A	B	C	D	E
1	Cd. Aux./Dpto.	Balances AIFRS (AUD) January	Balances AIFRS (AUD) February	Balances AIFRS (AUD) March	Balances AIFRS (AUD) April
2	552300	41,650.16	40,436.41	110,196.15	114,788.05
3	552315	-1,387,469.69	-1,387,469.69	-1,387,469.69	-1,387,469.69
4	552301	-4,321,908.98	-4,321,908.98	-4,321,908.98	-4,321,908.98
5	552301	-2,789,625.35	-2,789,625.35	-2,789,625.35	-2,789,625.35
6	552301	-41,650.16	-40,436.41	-41,613.89	-43,347.95
7	552301	0	0	-68,582.26	-71,440.10
8	552302	0	0	0	0
9	552303	-2,789,625.35	-2,789,625.35	-2,789,625.35	-2,789,625.35
10	552304	-2,789,625.35	-2,789,625.35	-2,789,625.35	-2,789,625.35
11
12
13		Month	March
14		Cd. Aux./Dpto.	552301
15		Amount	(7,221,730.48)
Sheet5

Cell Formulas
Range	Formula
C15	=SUMPRODUCT((A2:A10=C14)*(INDEX(B2:E10,,MONTH(C13))))

 

[mophsus]

Many thanks, appreciate very much your help, i've tried and it works

 

[mophsus]

Many thanks, appreciate very much your help, i've tried and it works fine

 

[DanteAmor]

Many thanks, appreciate very much your help, i've tried and it works fine

I'm glad to help you. Thanks for the feedback.