sum 2D data in one sheet from another sheet

m_griff13

New Member
Joined
Oct 23, 2018
Messages
1
[TABLE="class: grid, width: 500, align: right"]
<tbody>[TR]
[TD]Account
[/TD]
[TD]CC
[/TD]
[TD]Acc-CC
[/TD]
[TD]Descr
[/TD]
[TD]Jan
[/TD]
[TD]Feb
[/TD]
[TD]Mar
[/TD]
[TD]Apr
[/TD]
[/TR]
[TR]
[TD]301000
[/TD]
[TD]000
[/TD]
[TD]301000 000
[/TD]
[TD]Alt Rev
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]301000
[/TD]
[TD]100
[/TD]
[TD]301000 100
[/TD]
[TD]Alt Rev
[/TD]
[TD]500
[/TD]
[TD]500
[/TD]
[TD]200
[/TD]
[TD]400
[/TD]
[/TR]
[TR]
[TD]301001
[/TD]
[TD]000
[/TD]
[TD]301001 000
[/TD]
[TD]Revenue
[/TD]
[TD]5000
[/TD]
[TD]8000
[/TD]
[TD]10000
[/TD]
[TD]6000
[/TD]
[/TR]
[TR]
[TD]301001
[/TD]
[TD]100
[/TD]
[TD]301001 100
[/TD]
[TD]Revenue
[/TD]
[TD]0
[/TD]
[TD]2000
[/TD]
[TD]5000
[/TD]
[TD]8000
[/TD]
[/TR]
[TR]
[TD]301001
[/TD]
[TD]200
[/TD]
[TD]301001 200
[/TD]
[TD]Revenue
[/TD]
[TD]10000
[/TD]
[TD]5000
[/TD]
[TD]2000
[/TD]
[TD]6000
[/TD]
[/TR]
[TR]
[TD]310000
[/TD]
[TD]000
[/TD]
[TD]310000 000
[/TD]
[TD]Track Rev
[/TD]
[TD]2000
[/TD]
[TD]6000
[/TD]
[TD]5000
[/TD]
[TD]4000
[/TD]
[/TR]
[TR]
[TD]310000
[/TD]
[TD]100
[/TD]
[TD]310000 100
[/TD]
[TD]Track Rev
[/TD]
[TD]8000
[/TD]
[TD]4000
[/TD]
[TD]5000
[/TD]
[TD]6000
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500, align: right"]
<tbody>[TR]
[TD]2nd Table
[/TD]
[TD]Feb
[/TD]
[TD]%of Rev
[/TD]
[TD]Prior Feb
[/TD]
[TD]Q1
[/TD]
[TD]Q2
[/TD]
[/TR]
[TR]
[TD]301000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]301001
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]310000 000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]310000 100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I have a large set of data on a Sheet{Curr_Data}E6:Q1000, and on another Sheet {Trend} I would like to sum by account, for a particular month or account-CC . Say all account 301001 and for Feb this month and Mar for next month. In 2nd Sheet I would like to have a formula that would be looking to sum all 301001 for Feb based on the second sheet below. Then, when I switch months give me Mar and so on. As you can see some accounts are summarized by account & others by Account-CC. I need a formula that is dynamic with 2nd table I have tried SUMPRODUCT: =SUMPRODUCT((Acct=$B23)*(Mnth=E$20)*Data) Named ranges.

Thank you.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Forum statistics

Threads
1,223,886
Messages
6,175,190
Members
452,616
Latest member
intern444

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