Chart Question reference filtering data

[lilsnoop]

Can anybody advise how I might be able to have a chart change or adjust when filtering data occurs? To explain I have a column (F6 to F2000) that contains data, that I want graphed/charted. I created a range of cells, which I use a Sumproduct formula to identify how many of each particular item is contained in F6 to F2000 and thus it gives me my percentages, for charting purposes, etc. The problem that I have encountered is that I have a column specifically designated for month, day & Year to give me many ways to filter the many columns of data I have in this spreadsheet. The only data I need charted though is in column F6 as indicated above. In an effort to have monthly reports based on this chartable data I was hoping that if I were to filter by month and year, etc, I could obtain the sumproduct of that visible data in column F6-F2000. Currently though it seems to keep all my sumproduct data even for those cells hidden during the filtering process. Any suggestions would be greatly appreciated!

[Bryan Hessey]

Can anybody advise how I might be able to have a chart change or adjust when filtering data occurs? To explain I have a column (F6 to F2000) that contains data, that I want graphed/charted. I created a range of cells, which I use a Sumproduct formula to identify how many of each particular item is contained in F6 to F2000 and thus it gives me my percentages, for charting purposes, etc. The problem that I have encountered is that I have a column specifically designated for month, day & Year to give me many ways to filter the many columns of data I have in this spreadsheet. The only data I need charted though is in column F6 as indicated above. In an effort to have monthly reports based on this chartable data I was hoping that if I were to filter by month and year, etc, I could obtain the sumproduct of that visible data in column F6-F2000. Currently though it seems to keep all my sumproduct data even for those cells hidden during the filtering process. Any suggestions would be greatly appreciated!

Hi,

not sure how you would go about setting SumProduct to include only non-hidden items except by the use of a helper column which had something like

=Subtotal(102,F1)

which, when formula filled down the column, would show 1 for any non-hidden row where column F was not zero.
This could then be included in your SumProduct to give the reduced value.

hth
---

[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!

[Bryan Hessey]

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

[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!

[Bryan Hessey]

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

---

[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!

[Bryan Hessey]

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.

---