sum rows in range change column

M

mophsus

New Member
Joined
Jun 13, 2019
Messages
3
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

[TABLE="width: 500"]
<tbody>[TR]
[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]
[TD]552300[/TD]
[TD]41,650.16[/TD]
[TD]40,436.41[/TD]
[TD]110,196.15[/TD]
[TD]114,788.05[/TD]
[/TR]
[TR]
[TD]552315[/TD]
[TD]-1,387,469.69[/TD]
[TD]-1,387,469.69[/TD]
[TD]-1,387,469.69[/TD]
[TD]-1,387,469.69[/TD]
[/TR]
[TR]
[TD]552301[/TD]
[TD]-4,321,908.98[/TD]
[TD]-4,321,908.98[/TD]
[TD]-4,321,908.98[/TD]
[TD]-4,321,908.98[/TD]
[/TR]
[TR]
[TD]552301[/TD]
[TD]-2,789,625.35[/TD]
[TD]-2,789,625.35[/TD]
[TD]-2,789,625.35[/TD]
[TD]-2,789,625.35[/TD]
[/TR]
[TR]
[TD]552301[/TD]
[TD]-41,650.16[/TD]
[TD]-40,436.41[/TD]
[TD]-41,613.89[/TD]
[TD]-43,347.95[/TD]
[/TR]
[TR]
[TD]552301[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]-68,582.26[/TD]
[TD]-71,440.10[/TD]
[/TR]
[TR]
[TD]552302[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]552303[/TD]
[TD]-2,789,625.35[/TD]
[TD]-2,789,625.35[/TD]
[TD]-2,789,625.35[/TD]
[TD]-2,789,625.35[/TD]
[/TR]
[TR]
[TD]552304[/TD]
[TD]-2,789,625.35[/TD]
[TD]-2,789,625.35[/TD]
[TD]-2,789,625.35[/TD]
[TD]-2,789,625.35[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

and in my report i change the month

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Balances AIFRS (AUD) March[/TD]
[/TR]
[TR]
[TD]552301[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Next month it will be:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Balances AIFRS (AUD) April[/TD]
[/TR]
[TR]
[TD]552301[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

Thanks in advance
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
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).

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

and it returned -7,221,730.48
 
Last edited:
Upvote 0
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>
 
Upvote 0

Book1
ABCDE
1Cd. Aux./Dpto.Balances AIFRS (AUD) JanuaryBalances AIFRS (AUD) FebruaryBalances AIFRS (AUD) MarchBalances AIFRS (AUD) April
255230041,650.1640,436.41110,196.15114,788.05
3552315-1,387,469.69-1,387,469.69-1,387,469.69-1,387,469.69
4552301-4,321,908.98-4,321,908.98-4,321,908.98-4,321,908.98
5552301-2,789,625.35-2,789,625.35-2,789,625.35-2,789,625.35
6552301-41,650.16-40,436.41-41,613.89-43,347.95
755230100-68,582.26-71,440.10
85523020000
9552303-2,789,625.35-2,789,625.35-2,789,625.35-2,789,625.35
10552304-2,789,625.35-2,789,625.35-2,789,625.35-2,789,625.35
11
12
13MonthMarch
14Cd. Aux./Dpto.552301
15Amount(7,221,730.48)
Sheet5
Cell Formulas
RangeFormula
C15=SUMPRODUCT((A2:A10=C14)*(INDEX(B2:E10,,MONTH(C13))))
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
VBA Copy/Paste formula from D1 into empty cells in column D, starting at D6 and going down to last row
Replies
1
Views
1K
Fluff
Fluff
D
  • Question Question
Dynamic count by month over several years - excel 2016
Replies
2
Views
394
dave bates
D
S
  • Question Question
Conditional Sum in multi column multi row range
Replies
3
Views
365
Aladin Akyurek
Aladin Akyurek
A
  • Question Question
Data summary - transfer certain columns of rows 2 criteria (VBA or Formula)
Replies
1
Views
682
Aberdham
A
D
  • Question Question
Sum a Specific Column up to a Specific Row
Replies
5
Views
1K
dsison18
D

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top