Table calc with a slicer

M

Mike_Alex

New member
Joined
Jun 10, 2014
Messages
57
Reaction score
0
Points
0
Location
Middle Wisconsin
I was a little thrown back by this one, thought it was just an automatic. I built a simple spreadsheet with a table in it and used a bunch of sumif and sumifs to pull the table data together (used the table column names in the calc). I inserted a slicer to filter one of the columns, but found that if you have a table and perform a sumif or sumifs on it, it will return the column total without the slicer selection. Is it possible that I missed a setting or can you really not sum up a table column with a slicer value as a criteria? I find it hard to believe, hence why I ask.
 
Mike_Alex said:
I was a little thrown back by this one, thought it was just an automatic. I built a simple spreadsheet with a table in it and used a bunch of sumif and sumifs to pull the table data together (used the table column names in the calc). I inserted a slicer to filter one of the columns, but found that if you have a table and perform a sumif or sumifs on it, it will return the column total without the slicer selection. Is it possible that I missed a setting or can you really not sum up a table column with a slicer value as a criteria? I find it hard to believe, hence why I ask.
Click to expand...

Hello Mike
Without seeing some sample data illustrating the problem, Im not sure. Is your table a pivot table, and if not have you tried one?
 
Not a pivot table, just a table. I was hoping not to have to create a table, then a pivot table on top of that, just to filter the data, but ended up going that route. I was hoping just to be able to put in a sumif calculation against the table, then use the slicer as the filter. Basically the formula would be =sumifs(Table[Table Column],Criteria_range1,Criteria1,Criteria_range2,Criteria2), and then the slicer would filter the table.
 
Hello Mike
Based on what your saying, your "table" could be regarded as an extract of data, very similar to stuff Ive compiled to illustrate the use of formula (and pivot tables). I haven't seen your data, but I would think that from where you are, a pivot table should be the quickest and easiest way to filter the data. Having said that I can't really explain why your solution is not working without seeing an example.
 
Mike, the SUMIF is referencing the table but is not part of it, so the slicer doesn't impact it (exactly the same as a filtered list, SUMIF still sums hidden itemes). If you only want the visible items you could use SUBTOTAL(9,..., but you would need to use a SUMPRODUCT variant if you want some criteria).
 
alright, so how can I add an attachment to this? Then I can show you what I am looking at? The attachment symbol is faded out. Sumproduct did not work (or, I simply am unfamiliar with that calc).
 
Click the Go To Advanced button, then Manage Attachments
 
ah hah he says! Thank you sir. Attached the book. Hopefully this explains it a little better.
 

Attachments

  • Table with slicer.xlsx
    11.8 KB · Views: 186
again, all I was looking to do was a simple report that would give me the total in the table, and then use the slicer as the filter.
 
Mike
Ill leave you with Bob, as my version of Excel doesn't support slicers, so I only have a blank shape. :)
 
Sorry :redface:
Couldn't resist playing about with a Pivot Table !
 

Attachments

  • Table with slicer.xlsx
    16 KB · Views: 27
Try this Mike

=SUMPRODUCT(SUBTOTAL(9,OFFSET(Table1[[#Headers],[Sales]],ROW(Table1[[#All],[Sales]])-ROW(Table1[[#Headers],[Sales]]),,1)),--(Table1[[#All],[date]]=B20))
 
Another possibility - Having set up the Pivot Table rather than a standard table, you can then connect slicers to it (as the attachment) provided your working with Excel 2010 and later. If you want to use slicers on other tables, you need Excel 2013.
 

Attachments

  • Table with slicer.xlsx
    16.7 KB · Views: 42
With a pivot table, I could use the slicer, the just use a getpivotdata formula, but was really trying to avoid the pivot table altogether. However! Bobs solution worked like a charm. Does bring up another question tho, what does the double dash represent in this formula? I have never used that one.
 
Hello Mike
Glad we found the right solution for you. The double unary is a way to convert a string representation of a number into its number form (i.e. "123" into 123).
 
Interesting. One thing I put into charts is a text box under the chart to pull data in so they can see what the number is (such as Last weeks sales: N Average: N and o forth). To do this I have to put together a formula to concatenate the text along with the cell address to create the text. Calc works fine, right up till someone wanted me to insert commas into the number. Now I have to use the LEN along with the ROUND and CONCATENATE just to pull all the info together. Is it possible to just use the "unary" as you call it to do the same thing? Turn the number section into a number so it would automatically format as a number?
 
Mike_Alex said:
Interesting. One thing I put into charts is a text box under the chart to pull data in so they can see what the number is (such as Last weeks sales: N Average: N and o forth). To do this I have to put together a formula to concatenate the text along with the cell address to create the text. Calc works fine, right up till someone wanted me to insert commas into the number. Now I have to use the LEN along with the ROUND and CONCATENATE just to pull all the info together. Is it possible to just use the "unary" as you call it to do the same thing? Turn the number section into a number so it would automatically format as a number?
Click to expand...

Im inclined to think that it wouldn't work on its own, and in any case you can achieve the same end result by performing any mathematical manipulation. The easiest is to add zero to the string numeric, which doesn't alter the value of the resulting number.
I suggest that you open a new thread for this latest problem with an example showing the expected and actual results
 
You must log in or register to reply here.
Back
Top