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]

 

[Hoube78]

Amazing,

However i think i was not clear i dont need four seperate formula but one as the user would apply a filter to to the table.

e.g. the formula would subtotal only visible rows with cash and then i would have another formula which would subtotal only the rows visible for revenue.

i'm guessing i just need to remove the part which points towards columns B and D?

Thankyou think you have got me started and so will have a go / play in a couple of hours.

Thanks,
John

 

[Aladin Akyurek]

Amazing,

However i think i was not clear i dont need four seperate formula but one as the user would apply a filter to to the table.

e.g. the formula would subtotal only visible rows with cash and then i would have another formula which would subtotal only the rows visible for revenue.

i'm guessing i just need to remove the part which points towards columns B and D?

Thankyou think you have got me started and so will have a go / play in a couple of hours.

Thanks,
John

You are still not very clear. Judging from what you seem to say above, you probably just need SUBTOTAL()...

 

[Hoube78]

Hi sorry,

If I subtotal the column I would not get the CASH and REVENUE results broken down? I would get the overall average.

I have an excel table which is hidden from the users on another sheet, On a report sheet I have the Titles Cash and Revenue and the slicers for Region, Area and Title.

The user would select an option for the slicer and the Cash and Revenue figures would update. So what I need is a Subtotal formula with an if clause in it to only average the rows visible in the table for Cash and Revenue?

I cant attach an excel workbook otherwise I would which would make it clear a for you sorry.

John

 

[Hoube78]

Hi,

Not sure if this will make it any clearer for you but here is the formula:

=IF(Table1[[#All],[SECTION_NAME]]=[@[SECTION_NAME]],SUBTOTAL(1,Table1[[#All],[RC - 1 RESULTS]]),"")

 

[Hoube78]

You are still not very clear. Judging from what you seem to say above, you probably just need SUBTOTAL()...

Hi thanks for your help today,

I'm nearly got it to work??

using:
=AVERAGE(IF(Table1[[#All],[SECTION_NAME]]=[@[SECTION_NAME]],IF(SUBTOTAL(102,OFFSET($G$12,ROW(Table1[RC - 1 RESULTS])-ROW(G$12),0)),Table1[RC - 1 RESULTS])))

However the issue I'm having is the results seem to be for the section below e.g. Revenue is below the Cash section the Revenue result appears in the Cash Result?

I have tested the above formula on my table and its working as I want apart form the result being for the wrong Section the one below it?

Thanks again for today you got me on the right track with an array.
John

 

[Aladin Akyurek]

Hi thanks for your help today,

I'm nearly got it to work??

using:
=AVERAGE(IF(Table1[[#All],[SECTION_NAME]]=[@[SECTION_NAME]],IF(SUBTOTAL(102,OFFSET($G$12,ROW(Table1[RC - 1 RESULTS])-ROW(G$12),0)),Table1[RC - 1 RESULTS])))

However the issue I'm having is the results seem to be for the section below e.g. Revenue is below the Cash section the Revenue result appears in the Cash Result?

I have tested the above formula on my table and its working as I want apart form the result being for the wrong Section the one below it?

Thanks again for today you got me on the right track with an array.
John

Convert Table1 to range, post upper part of 10 rows including the headers. And post the desired answer for that range.

 

[Hoube78]

Convert Table1 to range, post upper part of 10 rows including the headers. And post the desired answer for that range.

Thank you so much for your help with this, its all working as it should with he below formula:

=AVERAGE(IF(Table1[SECTION_NAME]=[@[SECTION_NAME]],IF(SUBTOTAL(102,OFFSET($G$13,ROW(Table1[RC - 1 RESULTS])-ROW(G$13),0,1)),Table1[RC - 1 RESULTS])))

Just in case someone views the above formula is entered as an array e.g. hold down Ctrl & Shift and press enter.

Saved me so much grief
J