Chart Question reference filtering data

Originally Posted by lilsnoop

I don't quite understand what you mean. If at all possible I've attached a sample of what I'm trying to chart and if you could show me what your suggestion is I'd sure be appreciative. Thanks again for your assistance!

Your Sumproduct seems to make no sense, as although it is listed under the same heading as column F it seems to be unrelated on a row-by-row basis.
Does the comment in cell G5 refer to a Row or a Column?

If you filter by any of the selectable filters you may lose visibility to any portion or all of the G6:G19 range.

I have converted your G6:G19 range to 'fillable' formula, you will then need to hide G H and I columns, but amendments should be easier.

Column I is =1 if the row (specifically the C column) is visible.

This was added to the Sumproduct to total only Visible rows, which I think was your original question, however, as noted above you may or may not be able to see those totals.

hth
---

Originally Posted by lilsnoop

Bryan, to answer your questions; I meant to eventually hide "column G" but I don't think it will be possible or my chart data will display inaccurately, if at all. Column F6 and below has a drop down menu in each cell and the data options are listed in the drop down menu for that column. Column F6 and below is what I want to be charted. Ideally what I wanted to do is obtain a monthly pie chart of the various criminal activity for any given month of the year, which I thought I could do if I just filtered via my A5 filter option. My thought was to have column G add the sum product of each given drop down menu item in F6 and total them up, so I could have accurate percentages of the various criminal activity, but that doesn't work either. If I have four months of data (ie. jan, feb, march & april) and I filter the month of January to obtain my chart data for just that month. If I use your formula it still gives me a point for the other months even though they are hidden and under my previous formula it maintained the accurate percentages for all four months worth of data. So I'm still pulling my hair out, but I do appreciate the time you've spent helping me. Thanks again!

you are certainly confused, F6 is a cell, not a column.

If you use any filters you are going to hide some or all of the range F6:F65536, and question - why would you do a chart on column F?

Did you really mean that you wanted to chart column H names with column G statistics? . . thus giving, for each category, the total for the selected display?


added

The chart I would expect would normally look something like the attached.
To remove any empty columns, see http://www.peltiertech.com/Excel/Cha...zeColumns.html

---

Thanks Bryan! You were correct, I wanted the data from Column G and for charting purposes the names of the various crimes in Column H which has them listed up to (H6 to H19). Thank you for your advice and time!

Originally Posted by lilsnoop

Thanks Bryan! You were correct, I wanted the data from Column G and for charting purposes the names of the various crimes in Column H which has them listed up to (H6 to H19). Thank you for your advice and time!

np - I think you confused yourself with the hand-written formula, it is easier when you use a 'formula fill'able formula, but, good to see your problem resolved, and thanks for your response.

---