• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Structured Table - SUMPRODUCT - SUBTOTAL

mtr08004

New Member
Hello everyone. This really has me stumped. I recently started handling data in Excel using structured tables. I believe it would useful to create a working dashboard for customer entry and sales data. I dropped my data in which included:

Opportunity NameOWNERBusiness LineSegmentExpected Book DateSale Price
Opportunity Name 1BobInstallField
1/1/2020​
1000​
Opportunity Name 2JoeServiceCorporate
2/1/2020​
2000​
Opportunity Name 3BobInstallField
3/1/2020​
3000​
Opportunity Name 4BillServiceCorporate
4/1/2020​
4000​
Opportunity Name 5JimboInstallField
5/1/2020​
5000​
Opportunity Name 6JoeServiceCorporate
6/1/2020​
6000​
Opportunity Name 7BobInstallField
7/1/2020​
7000​
Opportunity Name 8JoeServiceCorporate
8/1/2020​
8000​


So --- I dropped in slicers for owner, business line, segment, etx.... no problem. This is to dynamically filer the results.

The output is a table, with months and total sales volume. per month...

November 1, 2019​
December 1, 2019​
January 1, 2020​
February 1, 2020​
March 1, 2020​
April 1, 2020​
May 1, 2020​
June 1, 2020​
$$$$$$$$$$$$$$$$$


The month does not change in the output.

I want to dynamically slice the and see the monthly total. I am able to easily accomplish this using a number methods using of subtotal, sumproduct, if statements... but I want to use the structured table as references..

My calculation goes like: IF MONTH = BOOKING MONTH, SUBTOTAL SLICED DATA

One solution to my problem is a formula like"

=SUMPRODUCT(SUBTOTAL(109,OFFSET(A2,ROW(A2:A281)-ROW(A2),,1)),--(C2:C281 = "Cat1"))

** works fine ** but I would like to embed the table reference but am unsure how to accomplish this.

Something like:

=SUMPRODUCT(--(MONTH(TABLE1[Expected Book Date])=MONTH(G$2)),TABLE1[@Sale Price])

Can't get it to work... I have tried other methods embedding IF statement and ditching SUMPRODUCT... no dice.... I think I just don't under stand how the table is converting to arrays...

Thoughts?

THANKS!!!!
 
Ok. So the error must be in my spreadsheet from finance. My math seems to work in the washed version that I just created here.
 

Attachments

  • Book1.xlsx
    16.1 KB · Views: 3
Yes. The data was formatted as general instead of date. Once I washed that column all worked fine. Thank you for the response.
 
Check that... I generated a new error. The way it is in the workbook does not subtotal per month. Which was my original issue. Fixing the format solved half the problem. I not get a real number but its for the entire data set not the sliced one. Sorry for the confusion. I've been working too much. LOL...
 
to get it sliced, try in [C14]
=SUMPRODUCT(SUBTOTAL(109,OFFSET(sales[[#Headers];[Sale Price]:[Sale Price]],ROW(sales[[Sale Price]:[Sale Price]])-ROW(sales[[#Headers],[Sale Price]:[Sale Price]]),,1)),--(MONTH(sales[[Expected Book Date]:[Expected Book Date]]) = MONTH(C13)))
 

Attachments

  • sumproduct subtotal with structured ref.xlsx
    16.2 KB · Views: 9
I added a 'Status' column
= SUBTOTAL(3,[@[Opportunity Name]])
to the table which shows 1 for a selected row and 0 for a hidden row.

That allowed the monthly figures to respond to the filter state
= SUMIFS(
sales[Sale Price],
sales[Expected Book Date], ">="&months,
sales[Expected Book Date], "<="&EOMONTH(+months, 0),
sales[Status], 1 )
 

Attachments

  • Aggregating filtered tables.xlsx
    24.1 KB · Views: 8
The problem I see is when does the pursuit of simplicity become an excuse for simplistic thinking?

It is possible to agree that simplicity is desirable but then move in opposite directions to achieve it.
 
Ah, 'smartly simple'; … I can live with that! :DD
The spreadsheets I hate are the ones that look like something the dog regurgitated … all speed and no thought! :eek:
 
Back
Top