Structured Table - SUMPRODUCT - SUBTOTAL

[mtr08004]

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 Name	OWNER	Business Line	Segment	Expected Book Date	Sale Price
Opportunity Name 1	Bob	Install	Field	1/1/2020​	1000​
Opportunity Name 2	Joe	Service	Corporate	2/1/2020​	2000​
Opportunity Name 3	Bob	Install	Field	3/1/2020​	3000​
Opportunity Name 4	Bill	Service	Corporate	4/1/2020​	4000​
Opportunity Name 5	Jimbo	Install	Field	5/1/2020​	5000​
Opportunity Name 6	Joe	Service	Corporate	6/1/2020​	6000​
Opportunity Name 7	Bob	Install	Field	7/1/2020​	7000​
Opportunity Name 8	Joe	Service	Corporate	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!!!!

 

[NARAYANK991]

Hi ,

Upload your workbook if possible.

Narayan

 

[mtr08004]

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.

 

[NARAYANK991]

Hi ,

Does that mean your problem is resolved ?

Narayan

 

[mtr08004]

Yes. The data was formatted as general instead of date. Once I washed that column all worked fine. Thank you for the response.

 

[mtr08004]

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

 

[GraH - Guido]

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

 

[mtr08004]

Thanks!!! I’ll be poking at this on Monday.

 

[Peter Bartholomew]

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 )

 

[GraH - Guido]

simplicity rules

 

[Peter Bartholomew]

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.

 

[GraH - Guido]

I meant, keeping it smartly simple, so passionate kissers we are .

 

[Peter Bartholomew]

Ah, 'smartly simple'; … I can live with that!
The spreadsheets I hate are the ones that look like something the dog regurgitated … all speed and no thought!