[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.
<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.