Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nesting sumproduct with subtotal
morning all,
I have a worksheet that I'm attempting to analyze, and wanted to see if a nested sumproduct with subtotal would work. I.e., the idea made sense to me but the application of it failed. I've tried the following, and neither work. =subtotal(109,sumproduct((RangeA=Criteria_RangeA)* (RangeB=Criteria_RangeB)*(SumRange))) I obtain an #N/A error. And I'm assuming that the reason I get the #N/A error is that I made my criteria a range instead of a single element. =sumproduct((RangeA=CritA)*(RangeB=CritB)*(Subtota l(109,SumRange))) Excel just flat out refuses to accept this one at all. I'm using Excel 2007. As to what I'm trying to accomplish. I'm not entirely sure on how to explain this, so I expect you'll still have questions to answer my need. And if you do initially understand it-- all the better. I have two worksheets. On one worksheet (Sheet A) I have a range of data that I'll call my source data. On the second worksheet (Sheet B), I'm filtering the data to only show specific data sets based on one of my criteria. I want to show the subtotal sum of the values that are located on Sheet A, which match the data that I'm working on, on Sheet B, and then do a subtotal of the whole thing. And as I consider this further, I'm thinking I may need to send you a copy of the worksheets because we can't do screen shots here, so you can see what I want to do. How can I accomplish doing something comparable to a sumproduct, and a subtotal between two worksheets that show me the subtotal of the specific data set and "grabs" all of the matching data on the other worksheet? Your helps are immensely appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nesting sumproduct with subtotal
No need for copies
=SUMPRODUCT(--(B2:B2000=Sheet3!A1),(SUBTOTAL(3,OFFSET($B$2,ROW(B 2:B2000)-MIN(ROW(B2:B2000)),,))),C2:C2000) say you want to sum C2:C2000 where B2:B2000 equals Sheet3 A1 and is filtered, the above assume the header is in row 1 and the data starts in row 2 -- Regards, Peo Sjoblom "SteveDB1" wrote in message ... morning all, I have a worksheet that I'm attempting to analyze, and wanted to see if a nested sumproduct with subtotal would work. I.e., the idea made sense to me but the application of it failed. I've tried the following, and neither work. =subtotal(109,sumproduct((RangeA=Criteria_RangeA)* (RangeB=Criteria_RangeB)*(SumRange))) I obtain an #N/A error. And I'm assuming that the reason I get the #N/A error is that I made my criteria a range instead of a single element. =sumproduct((RangeA=CritA)*(RangeB=CritB)*(Subtota l(109,SumRange))) Excel just flat out refuses to accept this one at all. I'm using Excel 2007. As to what I'm trying to accomplish. I'm not entirely sure on how to explain this, so I expect you'll still have questions to answer my need. And if you do initially understand it-- all the better. I have two worksheets. On one worksheet (Sheet A) I have a range of data that I'll call my source data. On the second worksheet (Sheet B), I'm filtering the data to only show specific data sets based on one of my criteria. I want to show the subtotal sum of the values that are located on Sheet A, which match the data that I'm working on, on Sheet B, and then do a subtotal of the whole thing. And as I consider this further, I'm thinking I may need to send you a copy of the worksheets because we can't do screen shots here, so you can see what I want to do. How can I accomplish doing something comparable to a sumproduct, and a subtotal between two worksheets that show me the subtotal of the specific data set and "grabs" all of the matching data on the other worksheet? Your helps are immensely appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nesting sumproduct with subtotal
Hi Peo,
Thank you for the response. I rewrote it to work for my form. Presently, I obtain an incorrect answer (I get zero, and I know that there is a non-zero value for this range of values I've subtotaled). So, I'm assuming that I did something incorrectly, or am not understanding something important. Sheet B is my filtered sheet. Sheet A is not filtered. This gets me to thinking that I may need to "reverse" my form of the equation you provided. Would this be correct? "Peo Sjoblom" wrote: No need for copies =SUMPRODUCT(--(B2:B2000=Sheet3!A1),(SUBTOTAL(3,OFFSET($B$2,ROW(B 2:B2000)-MIN(ROW(B2:B2000)),,))),C2:C2000) say you want to sum C2:C2000 where B2:B2000 equals Sheet3 A1 and is filtered, the above assume the header is in row 1 and the data starts in row 2 -- Regards, Peo Sjoblom "SteveDB1" wrote in message ... morning all, I have a worksheet that I'm attempting to analyze, and wanted to see if a nested sumproduct with subtotal would work. I.e., the idea made sense to me but the application of it failed. I've tried the following, and neither work. =subtotal(109,sumproduct((RangeA=Criteria_RangeA)* (RangeB=Criteria_RangeB)*(SumRange))) I obtain an #N/A error. And I'm assuming that the reason I get the #N/A error is that I made my criteria a range instead of a single element. =sumproduct((RangeA=CritA)*(RangeB=CritB)*(Subtota l(109,SumRange))) Excel just flat out refuses to accept this one at all. I'm using Excel 2007. As to what I'm trying to accomplish. I'm not entirely sure on how to explain this, so I expect you'll still have questions to answer my need. And if you do initially understand it-- all the better. I have two worksheets. On one worksheet (Sheet A) I have a range of data that I'll call my source data. On the second worksheet (Sheet B), I'm filtering the data to only show specific data sets based on one of my criteria. I want to show the subtotal sum of the values that are located on Sheet A, which match the data that I'm working on, on Sheet B, and then do a subtotal of the whole thing. And as I consider this further, I'm thinking I may need to send you a copy of the worksheets because we can't do screen shots here, so you can see what I want to do. How can I accomplish doing something comparable to a sumproduct, and a subtotal between two worksheets that show me the subtotal of the specific data set and "grabs" all of the matching data on the other worksheet? Your helps are immensely appreciated. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nesting sumproduct with subtotal
Post the formula that doesn't work
-- Regards, Peo Sjoblom "SteveDB1" wrote in message ... Hi Peo, Thank you for the response. I rewrote it to work for my form. Presently, I obtain an incorrect answer (I get zero, and I know that there is a non-zero value for this range of values I've subtotaled). So, I'm assuming that I did something incorrectly, or am not understanding something important. Sheet B is my filtered sheet. Sheet A is not filtered. This gets me to thinking that I may need to "reverse" my form of the equation you provided. Would this be correct? "Peo Sjoblom" wrote: No need for copies =SUMPRODUCT(--(B2:B2000=Sheet3!A1),(SUBTOTAL(3,OFFSET($B$2,ROW(B 2:B2000)-MIN(ROW(B2:B2000)),,))),C2:C2000) say you want to sum C2:C2000 where B2:B2000 equals Sheet3 A1 and is filtered, the above assume the header is in row 1 and the data starts in row 2 -- Regards, Peo Sjoblom "SteveDB1" wrote in message ... morning all, I have a worksheet that I'm attempting to analyze, and wanted to see if a nested sumproduct with subtotal would work. I.e., the idea made sense to me but the application of it failed. I've tried the following, and neither work. =subtotal(109,sumproduct((RangeA=Criteria_RangeA)* (RangeB=Criteria_RangeB)*(SumRange))) I obtain an #N/A error. And I'm assuming that the reason I get the #N/A error is that I made my criteria a range instead of a single element. =sumproduct((RangeA=CritA)*(RangeB=CritB)*(Subtota l(109,SumRange))) Excel just flat out refuses to accept this one at all. I'm using Excel 2007. As to what I'm trying to accomplish. I'm not entirely sure on how to explain this, so I expect you'll still have questions to answer my need. And if you do initially understand it-- all the better. I have two worksheets. On one worksheet (Sheet A) I have a range of data that I'll call my source data. On the second worksheet (Sheet B), I'm filtering the data to only show specific data sets based on one of my criteria. I want to show the subtotal sum of the values that are located on Sheet A, which match the data that I'm working on, on Sheet B, and then do a subtotal of the whole thing. And as I consider this further, I'm thinking I may need to send you a copy of the worksheets because we can't do screen shots here, so you can see what I want to do. How can I accomplish doing something comparable to a sumproduct, and a subtotal between two worksheets that show me the subtotal of the specific data set and "grabs" all of the matching data on the other worksheet? Your helps are immensely appreciated. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nesting sumproduct with subtotal
=SUMPRODUCT(--(A4:A195=Summary!C10),(SUBTOTAL(109,OFFSET($A$4,RO W(A4:A195)-MIN(ROW(A4:A195)),,))),C4:C195)
Column A is the owner name. This is my filtered worksheet On my Summary sheet, Column C is my owner name. This sheet is not filtered. With the use of the 109 in subtotal I'm seeking to subtotal the quantities that I've filtered-- which is Column C on my filtered sheet. "Peo Sjoblom" wrote: Post the formula that doesn't work -- Regards, Peo Sjoblom "SteveDB1" wrote in message ... Hi Peo, Thank you for the response. I rewrote it to work for my form. Presently, I obtain an incorrect answer (I get zero, and I know that there is a non-zero value for this range of values I've subtotaled). So, I'm assuming that I did something incorrectly, or am not understanding something important. Sheet B is my filtered sheet. Sheet A is not filtered. This gets me to thinking that I may need to "reverse" my form of the equation you provided. Would this be correct? "Peo Sjoblom" wrote: No need for copies =SUMPRODUCT(--(B2:B2000=Sheet3!A1),(SUBTOTAL(3,OFFSET($B$2,ROW(B 2:B2000)-MIN(ROW(B2:B2000)),,))),C2:C2000) say you want to sum C2:C2000 where B2:B2000 equals Sheet3 A1 and is filtered, the above assume the header is in row 1 and the data starts in row 2 -- Regards, Peo Sjoblom "SteveDB1" wrote in message ... morning all, I have a worksheet that I'm attempting to analyze, and wanted to see if a nested sumproduct with subtotal would work. I.e., the idea made sense to me but the application of it failed. I've tried the following, and neither work. =subtotal(109,sumproduct((RangeA=Criteria_RangeA)* (RangeB=Criteria_RangeB)*(SumRange))) I obtain an #N/A error. And I'm assuming that the reason I get the #N/A error is that I made my criteria a range instead of a single element. =sumproduct((RangeA=CritA)*(RangeB=CritB)*(Subtota l(109,SumRange))) Excel just flat out refuses to accept this one at all. I'm using Excel 2007. As to what I'm trying to accomplish. I'm not entirely sure on how to explain this, so I expect you'll still have questions to answer my need. And if you do initially understand it-- all the better. I have two worksheets. On one worksheet (Sheet A) I have a range of data that I'll call my source data. On the second worksheet (Sheet B), I'm filtering the data to only show specific data sets based on one of my criteria. I want to show the subtotal sum of the values that are located on Sheet A, which match the data that I'm working on, on Sheet B, and then do a subtotal of the whole thing. And as I consider this further, I'm thinking I may need to send you a copy of the worksheets because we can't do screen shots here, so you can see what I want to do. How can I accomplish doing something comparable to a sumproduct, and a subtotal between two worksheets that show me the subtotal of the specific data set and "grabs" all of the matching data on the other worksheet? Your helps are immensely appreciated. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nesting sumproduct with subtotal
No, you should use the formula I posted, don't change the 3 to 109 in your
formula, try =SUMPRODUCT(--(A4:A195=Summary!C10),--(SUBTOTAL(3,OFFSET($A$4,ROW(A4:A195)-MIN(ROW(A4:A195)),,))),C4:C195) you can also use 103 instead of the 3, however it will throw an error if you send the workbook or opened it in an earlier version of Excel. 3 and 103 in subtotal are equal except that 103 also disregards hidden rows like in formatrowshide. It was introduced in 2003 and unless you want to use it on hidden rows as well as filtered it is a good policy to use 3 to keep it compatible with previous versions. Don't use 9 or 109 in this formula, the subtotal part has nothing to do with totaling, it will create an array of unfiltered values that sumproduct will total according to your criteria -- Regards, Peo Sjoblom "SteveDB1" wrote in message ... =SUMPRODUCT(--(A4:A195=Summary!C10),(SUBTOTAL(109,OFFSET($A$4,RO W(A4:A195)-MIN(ROW(A4:A195)),,))),C4:C195) Column A is the owner name. This is my filtered worksheet On my Summary sheet, Column C is my owner name. This sheet is not filtered. With the use of the 109 in subtotal I'm seeking to subtotal the quantities that I've filtered-- which is Column C on my filtered sheet. "Peo Sjoblom" wrote: Post the formula that doesn't work -- Regards, Peo Sjoblom "SteveDB1" wrote in message ... Hi Peo, Thank you for the response. I rewrote it to work for my form. Presently, I obtain an incorrect answer (I get zero, and I know that there is a non-zero value for this range of values I've subtotaled). So, I'm assuming that I did something incorrectly, or am not understanding something important. Sheet B is my filtered sheet. Sheet A is not filtered. This gets me to thinking that I may need to "reverse" my form of the equation you provided. Would this be correct? "Peo Sjoblom" wrote: No need for copies =SUMPRODUCT(--(B2:B2000=Sheet3!A1),(SUBTOTAL(3,OFFSET($B$2,ROW(B 2:B2000)-MIN(ROW(B2:B2000)),,))),C2:C2000) say you want to sum C2:C2000 where B2:B2000 equals Sheet3 A1 and is filtered, the above assume the header is in row 1 and the data starts in row 2 -- Regards, Peo Sjoblom "SteveDB1" wrote in message ... morning all, I have a worksheet that I'm attempting to analyze, and wanted to see if a nested sumproduct with subtotal would work. I.e., the idea made sense to me but the application of it failed. I've tried the following, and neither work. =subtotal(109,sumproduct((RangeA=Criteria_RangeA)* (RangeB=Criteria_RangeB)*(SumRange))) I obtain an #N/A error. And I'm assuming that the reason I get the #N/A error is that I made my criteria a range instead of a single element. =sumproduct((RangeA=CritA)*(RangeB=CritB)*(Subtota l(109,SumRange))) Excel just flat out refuses to accept this one at all. I'm using Excel 2007. As to what I'm trying to accomplish. I'm not entirely sure on how to explain this, so I expect you'll still have questions to answer my need. And if you do initially understand it-- all the better. I have two worksheets. On one worksheet (Sheet A) I have a range of data that I'll call my source data. On the second worksheet (Sheet B), I'm filtering the data to only show specific data sets based on one of my criteria. I want to show the subtotal sum of the values that are located on Sheet A, which match the data that I'm working on, on Sheet B, and then do a subtotal of the whole thing. And as I consider this further, I'm thinking I may need to send you a copy of the worksheets because we can't do screen shots here, so you can see what I want to do. How can I accomplish doing something comparable to a sumproduct, and a subtotal between two worksheets that show me the subtotal of the specific data set and "grabs" all of the matching data on the other worksheet? Your helps are immensely appreciated. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nesting sumproduct with subtotal
Ok,
Got it.Thanks. Now for the next aspect of it. The way you've written it, it only takes the owner name for that one cell-- Summary!C10. My goal was to have it check the owner's name that I have active in my filtered sheet, and compare to all incidences of that owner's name on the summary sheet. Do I need to reverse the order of that element? I.e., instead of --(a4:a195=Summary!C10), should it become --(Summary!c10:c77=filtered_A4:a195)? where 'filtered_range' would show only the owner's name that's chosen in my filter. and if so, how would I write that? Again, thank you for your help. "Peo Sjoblom" wrote: No, you should use the formula I posted, don't change the 3 to 109 in your formula, try =SUMPRODUCT(--(A4:A195=Summary!C10),--(SUBTOTAL(3,OFFSET($A$4,ROW(A4:A195)-MIN(ROW(A4:A195)),,))),C4:C195) you can also use 103 instead of the 3, however it will throw an error if you send the workbook or opened it in an earlier version of Excel. 3 and 103 in subtotal are equal except that 103 also disregards hidden rows like in formatrowshide. It was introduced in 2003 and unless you want to use it on hidden rows as well as filtered it is a good policy to use 3 to keep it compatible with previous versions. Don't use 9 or 109 in this formula, the subtotal part has nothing to do with totaling, it will create an array of unfiltered values that sumproduct will total according to your criteria -- Regards, Peo Sjoblom "SteveDB1" wrote in message ... =SUMPRODUCT(--(A4:A195=Summary!C10),(SUBTOTAL(109,OFFSET($A$4,RO W(A4:A195)-MIN(ROW(A4:A195)),,))),C4:C195) Column A is the owner name. This is my filtered worksheet On my Summary sheet, Column C is my owner name. This sheet is not filtered. With the use of the 109 in subtotal I'm seeking to subtotal the quantities that I've filtered-- which is Column C on my filtered sheet. "Peo Sjoblom" wrote: Post the formula that doesn't work -- Regards, Peo Sjoblom "SteveDB1" wrote in message ... Hi Peo, Thank you for the response. I rewrote it to work for my form. Presently, I obtain an incorrect answer (I get zero, and I know that there is a non-zero value for this range of values I've subtotaled). So, I'm assuming that I did something incorrectly, or am not understanding something important. Sheet B is my filtered sheet. Sheet A is not filtered. This gets me to thinking that I may need to "reverse" my form of the equation you provided. Would this be correct? "Peo Sjoblom" wrote: No need for copies =SUMPRODUCT(--(B2:B2000=Sheet3!A1),(SUBTOTAL(3,OFFSET($B$2,ROW(B 2:B2000)-MIN(ROW(B2:B2000)),,))),C2:C2000) say you want to sum C2:C2000 where B2:B2000 equals Sheet3 A1 and is filtered, the above assume the header is in row 1 and the data starts in row 2 -- Regards, Peo Sjoblom "SteveDB1" wrote in message ... morning all, I have a worksheet that I'm attempting to analyze, and wanted to see if a nested sumproduct with subtotal would work. I.e., the idea made sense to me but the application of it failed. I've tried the following, and neither work. =subtotal(109,sumproduct((RangeA=Criteria_RangeA)* (RangeB=Criteria_RangeB)*(SumRange))) I obtain an #N/A error. And I'm assuming that the reason I get the #N/A error is that I made my criteria a range instead of a single element. =sumproduct((RangeA=CritA)*(RangeB=CritB)*(Subtota l(109,SumRange))) Excel just flat out refuses to accept this one at all. I'm using Excel 2007. As to what I'm trying to accomplish. I'm not entirely sure on how to explain this, so I expect you'll still have questions to answer my need. And if you do initially understand it-- all the better. I have two worksheets. On one worksheet (Sheet A) I have a range of data that I'll call my source data. On the second worksheet (Sheet B), I'm filtering the data to only show specific data sets based on one of my criteria. I want to show the subtotal sum of the values that are located on Sheet A, which match the data that I'm working on, on Sheet B, and then do a subtotal of the whole thing. And as I consider this further, I'm thinking I may need to send you a copy of the worksheets because we can't do screen shots here, so you can see what I want to do. How can I accomplish doing something comparable to a sumproduct, and a subtotal between two worksheets that show me the subtotal of the specific data set and "grabs" all of the matching data on the other worksheet? Your helps are immensely appreciated. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nesting sumproduct with subtotal
Gee! :)
=SUMPRODUCT(--(ISNUMBER(MATCH(A4:A195,Summary!C10:C77,0))),--(SUBTOTAL(3,OFFSET($A$4,ROW(A4:A195)-MIN(ROW(A4:A195)),,))),C4:C195) -- Regards, Peo Sjoblom "SteveDB1" wrote in message ... Ok, Got it.Thanks. Now for the next aspect of it. The way you've written it, it only takes the owner name for that one cell-- Summary!C10. My goal was to have it check the owner's name that I have active in my filtered sheet, and compare to all incidences of that owner's name on the summary sheet. Do I need to reverse the order of that element? I.e., instead of --(a4:a195=Summary!C10), should it become --(Summary!c10:c77=filtered_A4:a195)? where 'filtered_range' would show only the owner's name that's chosen in my filter. and if so, how would I write that? Again, thank you for your help. "Peo Sjoblom" wrote: No, you should use the formula I posted, don't change the 3 to 109 in your formula, try =SUMPRODUCT(--(A4:A195=Summary!C10),--(SUBTOTAL(3,OFFSET($A$4,ROW(A4:A195)-MIN(ROW(A4:A195)),,))),C4:C195) you can also use 103 instead of the 3, however it will throw an error if you send the workbook or opened it in an earlier version of Excel. 3 and 103 in subtotal are equal except that 103 also disregards hidden rows like in formatrowshide. It was introduced in 2003 and unless you want to use it on hidden rows as well as filtered it is a good policy to use 3 to keep it compatible with previous versions. Don't use 9 or 109 in this formula, the subtotal part has nothing to do with totaling, it will create an array of unfiltered values that sumproduct will total according to your criteria -- Regards, Peo Sjoblom "SteveDB1" wrote in message ... =SUMPRODUCT(--(A4:A195=Summary!C10),(SUBTOTAL(109,OFFSET($A$4,RO W(A4:A195)-MIN(ROW(A4:A195)),,))),C4:C195) Column A is the owner name. This is my filtered worksheet On my Summary sheet, Column C is my owner name. This sheet is not filtered. With the use of the 109 in subtotal I'm seeking to subtotal the quantities that I've filtered-- which is Column C on my filtered sheet. "Peo Sjoblom" wrote: Post the formula that doesn't work -- Regards, Peo Sjoblom "SteveDB1" wrote in message ... Hi Peo, Thank you for the response. I rewrote it to work for my form. Presently, I obtain an incorrect answer (I get zero, and I know that there is a non-zero value for this range of values I've subtotaled). So, I'm assuming that I did something incorrectly, or am not understanding something important. Sheet B is my filtered sheet. Sheet A is not filtered. This gets me to thinking that I may need to "reverse" my form of the equation you provided. Would this be correct? "Peo Sjoblom" wrote: No need for copies =SUMPRODUCT(--(B2:B2000=Sheet3!A1),(SUBTOTAL(3,OFFSET($B$2,ROW(B 2:B2000)-MIN(ROW(B2:B2000)),,))),C2:C2000) say you want to sum C2:C2000 where B2:B2000 equals Sheet3 A1 and is filtered, the above assume the header is in row 1 and the data starts in row 2 -- Regards, Peo Sjoblom "SteveDB1" wrote in message ... morning all, I have a worksheet that I'm attempting to analyze, and wanted to see if a nested sumproduct with subtotal would work. I.e., the idea made sense to me but the application of it failed. I've tried the following, and neither work. =subtotal(109,sumproduct((RangeA=Criteria_RangeA)* (RangeB=Criteria_RangeB)*(SumRange))) I obtain an #N/A error. And I'm assuming that the reason I get the #N/A error is that I made my criteria a range instead of a single element. =sumproduct((RangeA=CritA)*(RangeB=CritB)*(Subtota l(109,SumRange))) Excel just flat out refuses to accept this one at all. I'm using Excel 2007. As to what I'm trying to accomplish. I'm not entirely sure on how to explain this, so I expect you'll still have questions to answer my need. And if you do initially understand it-- all the better. I have two worksheets. On one worksheet (Sheet A) I have a range of data that I'll call my source data. On the second worksheet (Sheet B), I'm filtering the data to only show specific data sets based on one of my criteria. I want to show the subtotal sum of the values that are located on Sheet A, which match the data that I'm working on, on Sheet B, and then do a subtotal of the whole thing. And as I consider this further, I'm thinking I may need to send you a copy of the worksheets because we can't do screen shots here, so you can see what I want to do. How can I accomplish doing something comparable to a sumproduct, and a subtotal between two worksheets that show me the subtotal of the specific data set and "grabs" all of the matching data on the other worksheet? Your helps are immensely appreciated. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nesting sumproduct with subtotal
gee indeed......
=SUMPRODUCT(--(ISNUMBER(MATCH(A4:A195,Summary!C10:C77,0))),--(SUBTOTAL(3,OFFSET($A$4,ROW(A4:A195)-MIN(ROW(A4:A195)),,))),C4:C195) I copied it, and got a different value than expected. I know that "isnumber" returns a boolean. I know that "match" returns either the value, or an #N/A error if it cannot locate the value being sought. I know that "row" returns the number of the row the contents identified are located in. I've never used "offset", or "min." "Sumproduct" has become my favorite worksheet function because of its diversity, so perhaps I'm trying to get it to do something it's not the best tool for (that actually would not surprise me. I love tools, and am always looking for new projects to apply them to). With this configuration, would you please explain to me the processes taking place. Perhaps in doing so, I can more readily identify my problem. I've just never used these worksheet functions nested like this before. "Peo Sjoblom" wrote: Gee! :) =SUMPRODUCT(--(ISNUMBER(MATCH(A4:A195,Summary!C10:C77,0))),--(SUBTOTAL(3,OFFSET($A$4,ROW(A4:A195)-MIN(ROW(A4:A195)),,))),C4:C195) -- Regards, Peo Sjoblom "SteveDB1" wrote in message ... Ok, Got it.Thanks. Now for the next aspect of it. The way you've written it, it only takes the owner name for that one cell-- Summary!C10. My goal was to have it check the owner's name that I have active in my filtered sheet, and compare to all incidences of that owner's name on the summary sheet. Do I need to reverse the order of that element? I.e., instead of --(a4:a195=Summary!C10), should it become --(Summary!c10:c77=filtered_A4:a195)? where 'filtered_range' would show only the owner's name that's chosen in my filter. and if so, how would I write that? Again, thank you for your help. "Peo Sjoblom" wrote: No, you should use the formula I posted, don't change the 3 to 109 in your formula, try =SUMPRODUCT(--(A4:A195=Summary!C10),--(SUBTOTAL(3,OFFSET($A$4,ROW(A4:A195)-MIN(ROW(A4:A195)),,))),C4:C195) you can also use 103 instead of the 3, however it will throw an error if you send the workbook or opened it in an earlier version of Excel. 3 and 103 in subtotal are equal except that 103 also disregards hidden rows like in formatrowshide. It was introduced in 2003 and unless you want to use it on hidden rows as well as filtered it is a good policy to use 3 to keep it compatible with previous versions. Don't use 9 or 109 in this formula, the subtotal part has nothing to do with totaling, it will create an array of unfiltered values that sumproduct will total according to your criteria -- Regards, Peo Sjoblom "SteveDB1" wrote in message ... =SUMPRODUCT(--(A4:A195=Summary!C10),(SUBTOTAL(109,OFFSET($A$4,RO W(A4:A195)-MIN(ROW(A4:A195)),,))),C4:C195) Column A is the owner name. This is my filtered worksheet On my Summary sheet, Column C is my owner name. This sheet is not filtered. With the use of the 109 in subtotal I'm seeking to subtotal the quantities that I've filtered-- which is Column C on my filtered sheet. "Peo Sjoblom" wrote: Post the formula that doesn't work -- Regards, Peo Sjoblom "SteveDB1" wrote in message ... Hi Peo, Thank you for the response. I rewrote it to work for my form. Presently, I obtain an incorrect answer (I get zero, and I know that there is a non-zero value for this range of values I've subtotaled). So, I'm assuming that I did something incorrectly, or am not understanding something important. Sheet B is my filtered sheet. Sheet A is not filtered. This gets me to thinking that I may need to "reverse" my form of the equation you provided. Would this be correct? "Peo Sjoblom" wrote: No need for copies =SUMPRODUCT(--(B2:B2000=Sheet3!A1),(SUBTOTAL(3,OFFSET($B$2,ROW(B 2:B2000)-MIN(ROW(B2:B2000)),,))),C2:C2000) say you want to sum C2:C2000 where B2:B2000 equals Sheet3 A1 and is filtered, the above assume the header is in row 1 and the data starts in row 2 -- Regards, Peo Sjoblom "SteveDB1" wrote in message ... morning all, I have a worksheet that I'm attempting to analyze, and wanted to see if a nested sumproduct with subtotal would work. I.e., the idea made sense to me but the application of it failed. I've tried the following, and neither work. =subtotal(109,sumproduct((RangeA=Criteria_RangeA)* (RangeB=Criteria_RangeB)*(SumRange))) I obtain an #N/A error. And I'm assuming that the reason I get the #N/A error is that I made my criteria a range instead of a single element. =sumproduct((RangeA=CritA)*(RangeB=CritB)*(Subtota l(109,SumRange))) Excel just flat out refuses to accept this one at all. I'm using Excel 2007. As to what I'm trying to accomplish. I'm not entirely sure on how to explain this, so I expect you'll still have questions to answer my need. And if you do initially understand it-- all the better. I have two worksheets. On one worksheet (Sheet A) I have a range of data that I'll call my source data. On the second worksheet (Sheet B), I'm filtering the data to only show specific data sets based on one of my criteria. I want to show the subtotal sum of the values that are located on Sheet A, which match the data that I'm working on, on Sheet B, and then do a subtotal of the whole thing. And as I consider this further, I'm thinking I may need to send you a copy of the worksheets because we can't do screen shots here, so you can see what I want to do. How can I accomplish doing something comparable to a sumproduct, and a subtotal between two worksheets that show me the subtotal of the specific data set and "grabs" all of the matching data on the other worksheet? Your helps are immensely appreciated. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nesting sumproduct with subtotal
Hi Peo.
Ok, It appears to work now. I've tested 4 or 5 values, and it comes back with the numbers I'm expecting. Thank you for your help. It's deeply appreciated. Best. "Peo Sjoblom" wrote: Gee! :) =SUMPRODUCT(--(ISNUMBER(MATCH(A4:A195,Summary!C10:C77,0))),--(SUBTOTAL(3,OFFSET($A$4,ROW(A4:A195)-MIN(ROW(A4:A195)),,))),C4:C195) -- Regards, Peo Sjoblom "SteveDB1" wrote in message ... Ok, Got it.Thanks. Now for the next aspect of it. The way you've written it, it only takes the owner name for that one cell-- Summary!C10. My goal was to have it check the owner's name that I have active in my filtered sheet, and compare to all incidences of that owner's name on the summary sheet. Do I need to reverse the order of that element? I.e., instead of --(a4:a195=Summary!C10), should it become --(Summary!c10:c77=filtered_A4:a195)? where 'filtered_range' would show only the owner's name that's chosen in my filter. and if so, how would I write that? Again, thank you for your help. "Peo Sjoblom" wrote: No, you should use the formula I posted, don't change the 3 to 109 in your formula, try =SUMPRODUCT(--(A4:A195=Summary!C10),--(SUBTOTAL(3,OFFSET($A$4,ROW(A4:A195)-MIN(ROW(A4:A195)),,))),C4:C195) you can also use 103 instead of the 3, however it will throw an error if you send the workbook or opened it in an earlier version of Excel. 3 and 103 in subtotal are equal except that 103 also disregards hidden rows like in formatrowshide. It was introduced in 2003 and unless you want to use it on hidden rows as well as filtered it is a good policy to use 3 to keep it compatible with previous versions. Don't use 9 or 109 in this formula, the subtotal part has nothing to do with totaling, it will create an array of unfiltered values that sumproduct will total according to your criteria -- Regards, Peo Sjoblom "SteveDB1" wrote in message ... =SUMPRODUCT(--(A4:A195=Summary!C10),(SUBTOTAL(109,OFFSET($A$4,RO W(A4:A195)-MIN(ROW(A4:A195)),,))),C4:C195) Column A is the owner name. This is my filtered worksheet On my Summary sheet, Column C is my owner name. This sheet is not filtered. With the use of the 109 in subtotal I'm seeking to subtotal the quantities that I've filtered-- which is Column C on my filtered sheet. "Peo Sjoblom" wrote: Post the formula that doesn't work -- Regards, Peo Sjoblom "SteveDB1" wrote in message ... Hi Peo, Thank you for the response. I rewrote it to work for my form. Presently, I obtain an incorrect answer (I get zero, and I know that there is a non-zero value for this range of values I've subtotaled). So, I'm assuming that I did something incorrectly, or am not understanding something important. Sheet B is my filtered sheet. Sheet A is not filtered. This gets me to thinking that I may need to "reverse" my form of the equation you provided. Would this be correct? "Peo Sjoblom" wrote: No need for copies =SUMPRODUCT(--(B2:B2000=Sheet3!A1),(SUBTOTAL(3,OFFSET($B$2,ROW(B 2:B2000)-MIN(ROW(B2:B2000)),,))),C2:C2000) say you want to sum C2:C2000 where B2:B2000 equals Sheet3 A1 and is filtered, the above assume the header is in row 1 and the data starts in row 2 -- Regards, Peo Sjoblom "SteveDB1" wrote in message ... morning all, I have a worksheet that I'm attempting to analyze, and wanted to see if a nested sumproduct with subtotal would work. I.e., the idea made sense to me but the application of it failed. I've tried the following, and neither work. =subtotal(109,sumproduct((RangeA=Criteria_RangeA)* (RangeB=Criteria_RangeB)*(SumRange))) I obtain an #N/A error. And I'm assuming that the reason I get the #N/A error is that I made my criteria a range instead of a single element. =sumproduct((RangeA=CritA)*(RangeB=CritB)*(Subtota l(109,SumRange))) Excel just flat out refuses to accept this one at all. I'm using Excel 2007. As to what I'm trying to accomplish. I'm not entirely sure on how to explain this, so I expect you'll still have questions to answer my need. And if you do initially understand it-- all the better. I have two worksheets. On one worksheet (Sheet A) I have a range of data that I'll call my source data. On the second worksheet (Sheet B), I'm filtering the data to only show specific data sets based on one of my criteria. I want to show the subtotal sum of the values that are located on Sheet A, which match the data that I'm working on, on Sheet B, and then do a subtotal of the whole thing. And as I consider this further, I'm thinking I may need to send you a copy of the worksheets because we can't do screen shots here, so you can see what I want to do. How can I accomplish doing something comparable to a sumproduct, and a subtotal between two worksheets that show me the subtotal of the specific data set and "grabs" all of the matching data on the other worksheet? Your helps are immensely appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average nesting within Sumproduct? | Excel Worksheet Functions | |||
Sumproduct and subtotal | Excel Worksheet Functions | |||
Excel : Nesting of functions such as sumproduct and sumif | Excel Worksheet Functions | |||
subtotal nesting errors new this month | Excel Worksheet Functions | |||
How do I nesting subtotal function within average function in Exc | Excel Worksheet Functions |