Page 1 of 2 1 2 LastLast
Results 1 to 10 of 19

Thread: Table calc with a slicer

  1. #1
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Location
    Middle Wisconsin
    Posts
    57
    Articles
    0

    Table calc with a slicer



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by Mike_Alex View Post
    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. #3
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Location
    Middle Wisconsin
    Posts
    57
    Articles
    0
    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. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    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. #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. #6
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Location
    Middle Wisconsin
    Posts
    57
    Articles
    0
    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. #7
    Click the Go To Advanced button, then Manage Attachments

  8. #8
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Location
    Middle Wisconsin
    Posts
    57
    Articles
    0
    ah hah he says! Thank you sir. Attached the book. Hopefully this explains it a little better.
    Attached Files Attached Files

  9. #9
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Location
    Middle Wisconsin
    Posts
    57
    Articles
    0
    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. #10
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    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 LastLast

Posting Permissions

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