=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.