Subtotal with if function help

Hoube78

New Member
Joined
Mar 27, 2014
Messages
43
Hi,

I have a data set like below and need to create a subtotal of the sections. I cant use the sum if function as the table will be filtered and need the section subtotals to be dynamic in line with the table filter.

The filters are setup for BRAND,REGION,AREA and SITE. The idea is I have a separate chart which subtotals as the user applies filters

Any help please?

Thanks in Advance

[TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD]Col A
[/TD]
[TD]Col B
[/TD]
[TD]Col C
[/TD]
[TD]Col D
[/TD]
[TD]Col E
[/TD]
[TD]Col F
[/TD]
[TD]Col G
[/TD]
[TD]Col H
[/TD]
[/TR]
[TR]
[TD]BRAND
[/TD]
[TD]REGION
[/TD]
[TD]AREA
[/TD]
[TD]SITE
[/TD]
[TD]SITE NO
[/TD]
[TD]SECTION NAME
[/TD]
[TD]RC 1 RESULTS
[/TD]
[TD]RC 2 RESULTS
[/TD]
[/TR]
[TR]
[TD]client 1
[/TD]
[TD]North
[/TD]
[TD]Taz
[/TD]
[TD]Beds
[/TD]
[TD]01
[/TD]
[TD]CASH
[/TD]
[TD]80%
[/TD]
[TD]91%
[/TD]
[/TR]
[TR]
[TD]client 1
[/TD]
[TD]North
[/TD]
[TD]Taz
[/TD]
[TD]Beds
[/TD]
[TD]01
[/TD]
[TD]REVENUE
[/TD]
[TD]95%
[/TD]
[TD]82%
[/TD]
[/TR]
[TR]
[TD]client 1
[/TD]
[TD]North
[/TD]
[TD]Taz
[/TD]
[TD]HULL
[/TD]
[TD]02
[/TD]
[TD]CASH
[/TD]
[TD]81%
[/TD]
[TD]83%
[/TD]
[/TR]
[TR]
[TD]client 1
[/TD]
[TD]North
[/TD]
[TD]Taz
[/TD]
[TD]HULL
[/TD]
[TD]02
[/TD]
[TD]REVENUE
[/TD]
[TD]58%
[/TD]
[TD]94%
[/TD]
[/TR]
[TR]
[TD]client 1
[/TD]
[TD]US
[/TD]
[TD]BILL
[/TD]
[TD]NY
[/TD]
[TD]03
[/TD]
[TD]CASH
[/TD]
[TD]60%
[/TD]
[TD]99%
[/TD]
[/TR]
[TR]
[TD]client 1
[/TD]
[TD]US
[/TD]
[TD]BILL
[/TD]
[TD]NY
[/TD]
[TD]03
[/TD]
[TD]REVENUE
[/TD]
[TD]78%
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Hi,

Thanks but this wont solve my issue for getting the section results?

I would normally do sumif(CASH,COL F)

However this wont be dynamic when the table is filtered as it will include hidden rows.

I think i need to use sumproducts and then subtotal with an array?

However having never used array before i'm really lost.

Thanks for the idea
J
 
Upvote 0
Hi,

Sorry i thought i had,

I need the section subtotals which are located in colum F and the subtotal of the result RC - 1.

i basically have a sub table with the section names in (col A) which i would normally use if i was using sumif and reference this as to what excel needs to look for if i was using sumif. Which im not sure you can do with subtotal with an if clause?

let me know if the above is not enough info?

Thanks,
John
 
Upvote 0
Hi,

Sorry i thought i had,

I need the section subtotals which are located in colum F and the subtotal of the result RC - 1.

i basically have a sub table with the section names in (col A) which i would normally use if i was using sumif and reference this as to what excel needs to look for if i was using sumif. Which im not sure you can do with subtotal with an if clause?

let me know if the above is not enough info?

Thanks,
John

Are you wanting to sum RC-1 RESULTS while filter(s) are applied? If so, what are the additional conditions?
 
Upvote 0
Hi yes I want to no the average of result of RC - 1.

The table would be filtered by Region, Area and site. The plan is that as the user applies the filters they would get the result for Cash and Revenue.

If i use subtotal on the RC-1 this would work with filters but would only give me the overall average and I need to know the average broken down into sections e.g. Cash and Revenue.

At the moment I'm using subtotal with option 9 (average) on Columns G and H but need to get the average for Cash and Revenue e.g. adding an if clause.

Thanks,
J
 
Upvote 0
Hi

Without filters
Cash RC -1 =74%
Revenue RC - 1 =77%

With filter applied for Area and Taz selected
Cash RC -1 =80%
Revenue RC - 1 =76%

With filter applied by Region and US selected
Cash RC -1 =60%
Revenue RC - 1 =78%


Let me know if you want more?

Thanks,
John
 
Upvote 0
Hi

Without filters
Cash RC -1 =74%
Revenue RC - 1 =77%

With filter applied for Area and Taz selected
Cash RC -1 =80%
Revenue RC - 1 =76%

With filter applied by Region and US selected
Cash RC -1 =60%
Revenue RC - 1 =78%


Let me know if you want more?

Thanks,
John

Control+shift+enter, not just enter:
Rich (BB code):
=AVERAGE(IF(SUBTOTAL(2,OFFSET(G2,ROW(G2:G7)-ROW(G2),0,1)),
    IF(C2:C7="Taz",IF($F$2:$F$7="cash",G2:G7))))

Rich (BB code):
=AVERAGE(IF(SUBTOTAL(2,OFFSET(G2,ROW(G2:G7)-ROW(G2),0,1)),
    IF(C2:C7="Taz",IF($F$2:$F$7="revenue",G2:G7))))

Rich (BB code):
=AVERAGE(IF(SUBTOTAL(2,OFFSET(G2,ROW(G2:G7)-ROW(G2),0,1)),
    IF(B2:B7="US",IF($F$2:$F$7="cash",G2:G7))))

Rich (BB code):
=AVERAGE(IF(SUBTOTAL(2,OFFSET(G2,ROW(G2:G7)-ROW(G2),0,1)),
    IF(B2:B7="US",IF($F$2:$F$7="revenue",G2:G7))))
 
Upvote 0

Forum statistics

Threads
1,224,222
Messages
6,177,234
Members
452,765
Latest member
Erka Gizli

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