S
SteveDB1
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)*(Subtotal(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.
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)*(Subtotal(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.