# Thread: Table calc with a slicer

1. ## Table calc with a slicer

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.

2. Originally Posted by Mike_Alex
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.
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?

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

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

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

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

7. Click the Go To Advanced button, then Manage Attachments

8. ah hah he says! Thank you sir. Attached the book. Hopefully this explains it a little better.

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

10. Mike
Ill leave you with Bob, as my version of Excel doesn't support slicers, so I only have a blank shape.

Page 1 of 2 1 2 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•