nesting sumproduct with subtotal

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

 

[Peo Sjoblom]

No need for copies

=SUMPRODUCT(--(B2:B2000=Sheet3!A1),(SUBTOTAL(3,OFFSET($B$2,ROW(B2: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]

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]

Post the formula that doesn't work

--


Regards,


Peo Sjoblom

 

[SteveDB1]

=SUMPRODUCT(--(A4:A195=Summary!C10),(SUBTOTAL(109,OFFSET($A$4,ROW(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]

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
format>rows>hide.
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]

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]

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]

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.

 

[SteveDB1]

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.