Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 414
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 964
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 414
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 964
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 414
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 964
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 414
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 964
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 414
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 414
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Average nesting within Sumproduct? Twishlist Excel Worksheet Functions 3 July 26th 07 07:53 AM
Sumproduct and subtotal Marcelo Excel Worksheet Functions 1 March 21st 07 04:26 PM
Excel : Nesting of functions such as sumproduct and sumif Nimish Shah Excel Worksheet Functions 6 December 22nd 06 02:27 PM
subtotal nesting errors new this month Greenebush Excel Worksheet Functions 4 March 17th 06 11:35 PM
How do I nesting subtotal function within average function in Exc Amy Yeh Excel Worksheet Functions 4 January 19th 05 07:23 PM


All times are GMT +1. The time now is 10:55 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"