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:

	1	2	3	4
Account	Jan	Feb	Mar	Apr
61000	100	150	125	100
61000	200	200	150	175
61001	250	175	125	150
61001	150	150	125	125

<tbody>
</tbody>

The Summary worksheet is as follows:

	Month	3
Account	Amount
61000	(Calculated result - 175)
61001	(Calculated result - 150)
etc.	(Calculated result)

<tbody>
</tbody>

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
1		Month	3
2	Account	Amount
3	61000	275
4	61001	250
5	etc.

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Summary​

Worksheet Formulas Cell Formula B3 =SUMIF(Details!$A$3:$A$100,A3,INDEX(Details!$B$3:$Z$100,0,MATCH($C$1,Details!$B$1:$Z$1,0))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

You can subtract the 175 or 150 as needed.

 

[sandy666]

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

						Month	Mar
Account	Jan	Feb	Mar	Apr
61000​	100​	150​	125​	100​		Account	Sum of Value
61000​	200​	200​	150​	175​		61000	275​
61001​	250​	175​	125​	150​		61001	250​
61001​	150​	150​	125​	125​