Sum multiple rows with changing column

[zJenkins]

Hi,

I've seen variations of this question but haven't found anything that solves what I am trying to accomplish.

I have 2 worksheets, a Summary worksheet, and a Details worksheet. The summary worksheet summarizes what is on the detail worksheet (pretty crazy eh??).

The Details worksheet is as follows:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Account[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[/TR]
[TR]
[TD]61000[/TD]
[TD]100[/TD]
[TD]150[/TD]
[TD]125[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]61000[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]150[/TD]
[TD]175[/TD]
[/TR]
[TR]
[TD]61001[/TD]
[TD]250[/TD]
[TD]175[/TD]
[TD]125[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]61001[/TD]
[TD]150[/TD]
[TD]150[/TD]
[TD]125[/TD]
[TD]125[/TD]
[/TR]
</tbody>[/TABLE]


The Summary worksheet is as follows:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Month[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Account[/TD]
[TD]Amount[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]61000
[/TD]
[TD](Calculated result - 175)
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]61001[/TD]
[TD](Calculated result - 150)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]etc.[/TD]
[TD](Calculated result)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


On the Summary worksheet, C1 (value = 3 in the example above), this value is able to be changed by the user, they can select which month they want to see the results for. It was originally to be used as a helper value for an Index/Match formula to return the value in the March (3) column, or whatever month was selected. The issue is that when an account has multiple lines in the Details worksheet, such as 61000 and 61001 in the above example, the Index/Match formula doesn't work because there are multiple values. I'm wondering if there is a way to incorporate the SUMIF function to get the desired result...

Any help would be greatly appreciated.

Thanks!

 

[Eric W]

Perhaps:

	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Month[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Account[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Amount[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]61000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]275[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]61001[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]250[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]etc.[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

</tbody>

Summary​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=SUMIF(Details!$A$3:$A$100,A3,INDEX(Details!$B$3:$Z$100,0,MATCH($C$1,Details!$B$1:$Z$1,0)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



You can subtract the 175 or 150 as needed.

 

[sandy666]

UnPivot source table via PowerQuery then use PivotTable with QueryTable with Month as filter

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#DDEBF7]Month[/td][td=bgcolor:#DDEBF7]Mar[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Account[/td][td=bgcolor:#5B9BD5]Jan[/td][td=bgcolor:#5B9BD5]Feb[/td][td=bgcolor:#5B9BD5]Mar[/td][td=bgcolor:#5B9BD5]Apr[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

61000​

[/td][td=bgcolor:#DDEBF7]

100​

[/td][td=bgcolor:#DDEBF7]

150​

[/td][td=bgcolor:#DDEBF7]

125​

[/td][td=bgcolor:#DDEBF7]

100​

[/td][td][/td][td=bgcolor:#DDEBF7]Account[/td][td=bgcolor:#DDEBF7]Sum of Value[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

61000​

[/td][td]

200​

[/td][td]

200​

[/td][td]

150​

[/td][td]

175​

[/td][td][/td][td]61000[/td][td]

275​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

61001​

[/td][td=bgcolor:#DDEBF7]

250​

[/td][td=bgcolor:#DDEBF7]

175​

[/td][td=bgcolor:#DDEBF7]

125​

[/td][td=bgcolor:#DDEBF7]

150​

[/td][td][/td][td]61001[/td][td]

250​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

61001​

[/td][td]

150​

[/td][td]

150​

[/td][td]

125​

[/td][td]

125​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]