Subtotal with if function help

[Hoube78]

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]

 

[FDibbins]

Perhaps try SUBTOTAL(109,range.............

 

[Hoube78]

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

 

[Aladin Akyurek]

You need to clearly state what range you want to sum and what condions must hold for which ranges.

 

[Hoube78]

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

 

[Aladin Akyurek]

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?

 

[Hoube78]

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

 

[Aladin Akyurek]

What is the answer for the sample you posted in post #1?

 

[Hoube78]

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

 

[Aladin Akyurek]

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:

=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))))

=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))))

=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))))

=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))))