Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Applying formulas to filtered data
Sorry if this is a basic question, but I can't find an answer within the help
menu. I have Office 2007. I have a worksheet with 119 rows and multiple columns of data. I have applied a filter to the first 4 columns (of the many - the rest of the colums just contain raw data I want to filter) so I can filter data by certain type/criteria. I want to apply simple formulas at the bottom of my overall data based on different filters. For Example, if I just want to sum up the entire column of my data, I'll do =sum(F6:F124). But then I also want to come up with the sum of the same column after I apply the filter. So, after I do the filter I may only have 12 data points (rows) that match the filter criteria. Those data do not come from sequential rows, but they come pretty randomly, so, if I do the sum of those data, and select the remaining cells, it will be =sum(F12:F83). The 12 data come from somewhere between rows 12 and 83, but the formula is summing ALL rows between 12 and 83... even though when the filter is applied and I select the cells for my SUM formula I only see 12 rows (row 12,13,14,15,32,33,54,55,80,81,82,83). Those are the only rows I want it to SUM. Is there a way to use these filters and also a formula to use to apply it only to the cells that are applicable to the filter, but not the cells that are filtered out inbetween? (I will also be doing similar simple formulas using division of different SUMs from different columns, etc.) Sorry for the long explanation, I was just trying to make my problem make sense. Thanks for the help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Applying formulas to filtered data
Instead of using SUM(, you should use SUBTOTAL(9, as in:
=SUBTOTAL(9,F6:F124) This will give you the sum of the displayed values after filtering, and will automatically adjust to a new filter being applied. You might like to put these totals at the top of the sheet (insert a new blank row above your filters) and use Window | Freeze Panes so they are always visible, and it saves you from keep scrolling down to the bottom to see the effect of applying a filter. Hope this helps. Pete On Aug 2, 12:40 am, Keith wrote: Sorry if this is a basic question, but I can't find an answer within the help menu. I have Office 2007. I have a worksheet with 119 rows and multiple columns of data. I have applied a filter to the first 4 columns (of the many - the rest of the colums just contain raw data I want to filter) so I can filter data by certain type/criteria. I want to apply simple formulas at the bottom of my overall data based on different filters. For Example, if I just want to sum up the entire column of my data, I'll do =sum(F6:F124). But then I also want to come up with the sum of the same column after I apply the filter. So, after I do the filter I may only have 12 data points (rows) that match the filter criteria. Those data do not come from sequential rows, but they come pretty randomly, so, if I do the sum of those data, and select the remaining cells, it will be =sum(F12:F83). The 12 data come from somewhere between rows 12 and 83, but the formula is summing ALL rows between 12 and 83... even though when the filter is applied and I select the cells for my SUM formula I only see 12 rows (row 12,13,14,15,32,33,54,55,80,81,82,83). Those are the only rows I want it to SUM. Is there a way to use these filters and also a formula to use to apply it only to the cells that are applicable to the filter, but not the cells that are filtered out inbetween? (I will also be doing similar simple formulas using division of different SUMs from different columns, etc.) Sorry for the long explanation, I was just trying to make my problem make sense. Thanks for the help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Applying formulas to filtered data
Pete,
Thank you very much for the help... I think it is on the right track for what I need. But, like you mentioned, it will automatically adjust to a new filter being applied. What about when I apply a different filter and need to sum the displayed values after the filter from the same column? One filter will display certain values in column F and I'll need to show the SUM of those, but then the next filter will display different values in column F and I'll need to show the SUM of those too. Is there a way to have different Sums of filtered values from the same column so it doesn't automatically update/change based on a different filter? Hope that question makes sense... Thanks, Keith "Pete_UK" wrote: Instead of using SUM(, you should use SUBTOTAL(9, as in: =SUBTOTAL(9,F6:F124) This will give you the sum of the displayed values after filtering, and will automatically adjust to a new filter being applied. You might like to put these totals at the top of the sheet (insert a new blank row above your filters) and use Window | Freeze Panes so they are always visible, and it saves you from keep scrolling down to the bottom to see the effect of applying a filter. Hope this helps. Pete On Aug 2, 12:40 am, Keith wrote: Sorry if this is a basic question, but I can't find an answer within the help menu. I have Office 2007. I have a worksheet with 119 rows and multiple columns of data. I have applied a filter to the first 4 columns (of the many - the rest of the colums just contain raw data I want to filter) so I can filter data by certain type/criteria. I want to apply simple formulas at the bottom of my overall data based on different filters. For Example, if I just want to sum up the entire column of my data, I'll do =sum(F6:F124). But then I also want to come up with the sum of the same column after I apply the filter. So, after I do the filter I may only have 12 data points (rows) that match the filter criteria. Those data do not come from sequential rows, but they come pretty randomly, so, if I do the sum of those data, and select the remaining cells, it will be =sum(F12:F83). The 12 data come from somewhere between rows 12 and 83, but the formula is summing ALL rows between 12 and 83... even though when the filter is applied and I select the cells for my SUM formula I only see 12 rows (row 12,13,14,15,32,33,54,55,80,81,82,83). Those are the only rows I want it to SUM. Is there a way to use these filters and also a formula to use to apply it only to the cells that are applicable to the filter, but not the cells that are filtered out inbetween? (I will also be doing similar simple formulas using division of different SUMs from different columns, etc.) Sorry for the long explanation, I was just trying to make my problem make sense. Thanks for the help. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Applying formulas to filtered data
I think I know what you are asking for, but SUBTOTAL won't do that for
you - you will need to record one subtotal in another cell (out of the filter range, eg at the top) and then apply another filter. Another approach would be to build up a list of unique values from your filtered columns (perhaps in another sheet) and then you can build up a table by using SUMIF or SUMPRODUCT formulae and get values that way - if you want to follow this approach then you will need to supply some further details like which columns you are interested in and what values you have. Hope this helps. Pete On Aug 2, 1:32 am, Keith wrote: Pete, Thank you very much for the help... I think it is on the right track for what I need. But, like you mentioned, it will automatically adjust to a new filter being applied. What about when I apply a different filter and need to sum the displayed values after the filter from the same column? One filter will display certain values in column F and I'll need to show the SUM of those, but then the next filter will display different values in column F and I'll need to show the SUM of those too. Is there a way to have different Sums of filtered values from the same column so it doesn't automatically update/change based on a different filter? Hope that question makes sense... Thanks, Keith "Pete_UK" wrote: Instead of using SUM(, you should use SUBTOTAL(9, as in: =SUBTOTAL(9,F6:F124) This will give you the sum of the displayed values after filtering, and will automatically adjust to a new filter being applied. You might like to put these totals at the top of the sheet (insert a new blank row above your filters) and use Window | Freeze Panes so they are always visible, and it saves you from keep scrolling down to the bottom to see the effect of applying a filter. Hope this helps. Pete On Aug 2, 12:40 am, Keith wrote: Sorry if this is a basic question, but I can't find an answer within the help menu. I have Office 2007. I have a worksheet with 119 rows and multiple columns of data. I have applied a filter to the first 4 columns (of the many - the rest of the colums just contain raw data I want to filter) so I can filter data by certain type/criteria. I want to apply simple formulas at the bottom of my overall data based on different filters. For Example, if I just want to sum up the entire column of my data, I'll do =sum(F6:F124). But then I also want to come up with the sum of the same column after I apply the filter. So, after I do the filter I may only have 12 data points (rows) that match the filter criteria. Those data do not come from sequential rows, but they come pretty randomly, so, if I do the sum of those data, and select the remaining cells, it will be =sum(F12:F83). The 12 data come from somewhere between rows 12 and 83, but the formula is summing ALL rows between 12 and 83... even though when the filter is applied and I select the cells for my SUM formula I only see 12 rows (row 12,13,14,15,32,33,54,55,80,81,82,83). Those are the only rows I want it to SUM. Is there a way to use these filters and also a formula to use to apply it only to the cells that are applicable to the filter, but not the cells that are filtered out inbetween? (I will also be doing similar simple formulas using division of different SUMs from different columns, etc.) Sorry for the long explanation, I was just trying to make my problem make sense. Thanks for the help.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Applying formulas to filtered data
Pete,
I think SUMIF (or SUMIFS) is going to work for what I am trying to do. If I come across a problem in the future I will try to address it or ask further questions. Thanks for the help. "Pete_UK" wrote: I think I know what you are asking for, but SUBTOTAL won't do that for you - you will need to record one subtotal in another cell (out of the filter range, eg at the top) and then apply another filter. Another approach would be to build up a list of unique values from your filtered columns (perhaps in another sheet) and then you can build up a table by using SUMIF or SUMPRODUCT formulae and get values that way - if you want to follow this approach then you will need to supply some further details like which columns you are interested in and what values you have. Hope this helps. Pete On Aug 2, 1:32 am, Keith wrote: Pete, Thank you very much for the help... I think it is on the right track for what I need. But, like you mentioned, it will automatically adjust to a new filter being applied. What about when I apply a different filter and need to sum the displayed values after the filter from the same column? One filter will display certain values in column F and I'll need to show the SUM of those, but then the next filter will display different values in column F and I'll need to show the SUM of those too. Is there a way to have different Sums of filtered values from the same column so it doesn't automatically update/change based on a different filter? Hope that question makes sense... Thanks, Keith "Pete_UK" wrote: Instead of using SUM(, you should use SUBTOTAL(9, as in: =SUBTOTAL(9,F6:F124) This will give you the sum of the displayed values after filtering, and will automatically adjust to a new filter being applied. You might like to put these totals at the top of the sheet (insert a new blank row above your filters) and use Window | Freeze Panes so they are always visible, and it saves you from keep scrolling down to the bottom to see the effect of applying a filter. Hope this helps. Pete On Aug 2, 12:40 am, Keith wrote: Sorry if this is a basic question, but I can't find an answer within the help menu. I have Office 2007. I have a worksheet with 119 rows and multiple columns of data. I have applied a filter to the first 4 columns (of the many - the rest of the colums just contain raw data I want to filter) so I can filter data by certain type/criteria. I want to apply simple formulas at the bottom of my overall data based on different filters. For Example, if I just want to sum up the entire column of my data, I'll do =sum(F6:F124). But then I also want to come up with the sum of the same column after I apply the filter. So, after I do the filter I may only have 12 data points (rows) that match the filter criteria. Those data do not come from sequential rows, but they come pretty randomly, so, if I do the sum of those data, and select the remaining cells, it will be =sum(F12:F83). The 12 data come from somewhere between rows 12 and 83, but the formula is summing ALL rows between 12 and 83... even though when the filter is applied and I select the cells for my SUM formula I only see 12 rows (row 12,13,14,15,32,33,54,55,80,81,82,83). Those are the only rows I want it to SUM. Is there a way to use these filters and also a formula to use to apply it only to the cells that are applicable to the filter, but not the cells that are filtered out inbetween? (I will also be doing similar simple formulas using division of different SUMs from different columns, etc.) Sorry for the long explanation, I was just trying to make my problem make sense. Thanks for the help.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formulas, for filtered worksheet | Excel Discussion (Misc queries) | |||
Applying range names to existing formulas in separate worksheets | Excel Discussion (Misc queries) | |||
Applying data to a spreadsheet..help please | Excel Worksheet Functions | |||
Applying Formulas to Visible Cells Only | Excel Discussion (Misc queries) | |||
Applying formulas only to the subtotals of a data list | Excel Discussion (Misc queries) |