S
SteveDB1
howdee all.
Last month I'd asked about nesting a subtotal function with a sumproduct.
While my initial question was answered (the way I could explain it), I've
found something that it's not working with.
Here is the worksheet function in its final state.
=SUMPRODUCT(--(ISNUMBER(MATCH(A4:A18,SUM!C10:C77,0))),--(ISNUMBER(MATCH(F4:F18&"",SUM!A10:A77&"",0))),--(SUBTOTAL(3,OFFSET($A$4,ROW(A4:A18)-MIN(ROW(A4:A18)),,))),(C4:C18))
What I'm trying to do is to look at my sum sheet, and subtotal the
non-filtered values using my filtered range/values/criteria for the worksheet
the function is located on,which I'll call my APN sheet.
So, if I were to expand the formula to include the final location's sheet
name, it'd become
=SUMPRODUCT(--(ISNUMBER(MATCH(APN!A4:A18,SUM!C10:C77,0))),--(ISNUMBER(MATCH(APN!F4:F18&"",SUM!A10:A77&"",0))),--(SUBTOTAL(3,OFFSET(APN!$A$4,ROW(APN!A4:A18)-MIN(ROW(APN!A4:A18)),,))),(APN!C4:C18))
This function is not performing that operation.
What it is performing is the subtotal of the function on my APN sheet.
I've tweaked this formula 3 ways to Sunday, and it's not allowing me to look
at unfiltered values on sum, using my filtered values on APN.
Any ideas on how, or if my goal can be accomplished?
And if this form will not work in the long run, I can dump it for the use of
a better operation.
Again, for clarification, my desired goal is to subtotal non-filtered values
looking at filtered values, and sum the non-filtered values.
In the end, it's a comparison routine, in that I'm ensuring that one value
matches another set of values.
Thank you for your helps.
Best.
SteveB.
Last month I'd asked about nesting a subtotal function with a sumproduct.
While my initial question was answered (the way I could explain it), I've
found something that it's not working with.
Here is the worksheet function in its final state.
=SUMPRODUCT(--(ISNUMBER(MATCH(A4:A18,SUM!C10:C77,0))),--(ISNUMBER(MATCH(F4:F18&"",SUM!A10:A77&"",0))),--(SUBTOTAL(3,OFFSET($A$4,ROW(A4:A18)-MIN(ROW(A4:A18)),,))),(C4:C18))
What I'm trying to do is to look at my sum sheet, and subtotal the
non-filtered values using my filtered range/values/criteria for the worksheet
the function is located on,which I'll call my APN sheet.
So, if I were to expand the formula to include the final location's sheet
name, it'd become
=SUMPRODUCT(--(ISNUMBER(MATCH(APN!A4:A18,SUM!C10:C77,0))),--(ISNUMBER(MATCH(APN!F4:F18&"",SUM!A10:A77&"",0))),--(SUBTOTAL(3,OFFSET(APN!$A$4,ROW(APN!A4:A18)-MIN(ROW(APN!A4:A18)),,))),(APN!C4:C18))
This function is not performing that operation.
What it is performing is the subtotal of the function on my APN sheet.
I've tweaked this formula 3 ways to Sunday, and it's not allowing me to look
at unfiltered values on sum, using my filtered values on APN.
Any ideas on how, or if my goal can be accomplished?
And if this form will not work in the long run, I can dump it for the use of
a better operation.
Again, for clarification, my desired goal is to subtotal non-filtered values
looking at filtered values, and sum the non-filtered values.
In the end, it's a comparison routine, in that I'm ensuring that one value
matches another set of values.
Thank you for your helps.
Best.
SteveB.