In XL 2010 I have a table we can call Table1.

The table has labeled columns. One (at Column Q) is called "PnlRealized"
and one (at Column S) is called "Managed". The labels are on Row 3.

The table has thousands of rows of data.

Only some of the "Managed" column contain a text entry. It's the name of
a manager. The "PnlRealized" column is all currency numbers.

Column Q ... S
Row PnlRealized Managed
... ----------- --------
22 12,345.67
23 6,789.01 Smith
24 234.56
25 7,890.12 Jones
26 3,345.78
...

What I want in S1, above the table, is the SUBTOTAL sum for all
PnLRealized amounts that had managers assigned.

So in this case, that's 6,789.01 + 7890.12.

I know how to use the SUBTOTAL function and I know how to use
SUMPRODUCT, but I'm having trouble combining these the right way
to get what I want. (I think that's a good way to do what I want.)

Help?

I have tried:
=SUMPRODUCT(SUBTOTAL(103,Table1[Managed]),SUBTOTAL(109,Table1[PnlRealized]))

But that doesn't work. It gives me the sum of all PnlRealized figures
multiplied by the number of lines with a manager.

Thanks, folks....

/sh

Hi Sarah,

Am Thu, 8 Jan 2015 15:04:52 +0000 (UTC) schrieb Sarah H.:

=SUMPRODUCT(SUBTOTAL(103,Table1[Managed]),SUBTOTAL(109,Table1[PnlRealized]))

try:
=SUMPRODUCT(SUBTOTAL(9,INDIRECT("Q"&ROW(22:1000))) *(S22:S1000<""))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

In , Claus Busch
wrote:

Am Thu, 8 Jan 2015 15:04:52 +0000 (UTC) schrieb Sarah H.:

=SUMPRODUCT(SUBTOTAL(103,Table1[Managed]),SUBTOTAL(109,Table1[PnlRealized]))

try:
=SUMPRODUCT(SUBTOTAL(9,INDIRECT("Q"&ROW(22:1000))) *(S22:S1000<""))

Good, thank you, that works:
=SUMPRODUCT(SUBTOTAL(9,INDIRECT("q" & ROW(3:100000)))*(S3:S100000<""))

(There are 44,442 rows of data presently, so 1000 was too small.) :-)

Now please tell my why that works.

=ROW(3:100000) seems to be a horizontal area. I can't figure out
what you've done here. I do understand the INDIRECT function generally.

Also, what's the point of using a newfangled table (that presets
its data range nicely) if we just have to resort to old-fashioned
hard-coded (and "brittle") ranges with column letters? :-)

Much obliged, Claus.

/sh

Hi Sarah,

Am Thu, 8 Jan 2015 15:59:02 +0000 (UTC) schrieb Sarah H.:

=ROW(3:100000) seems to be a horizontal area. I can't figure out
what you've done here. I do understand the INDIRECT function generally.

Row(3:100000) is only a counter.

Also, what's the point of using a newfangled table (that presets
its data range nicely) if we just have to resort to old-fashioned
hard-coded (and "brittle") ranges with column letters? :-)

SUBTOTAL with a criteria does not work with fix ranges.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

In , Claus Busch
spake thusly:

Am Thu, 8 Jan 2015 15:59:02 +0000 (UTC) schrieb Sarah H.:

=ROW(3:100000) seems to be a horizontal area. I can't figure
out what you've done here. I do understand the INDIRECT
function generally.

Row(3:100000) is only a counter.

Huh. OK, I will have to study that further.

Also, what's the point of using a newfangled table (that
presets its data range nicely) if we just have to resort to
old-fashioned hard-coded (and "brittle") ranges with column
letters? :-)

SUBTOTAL with a criteria does not work with fix ranges.

Oh! That probably cost me a couple of hours of head-scratching. :-)

Vielen Dank, Claus! Das ist sagenhaft.

/sh

In , Sarah H.
wrote:

In , Claus Busch
wrote:

=SUMPRODUCT(SUBTOTAL(9,INDIRECT("Q"&ROW(22:1000))) *(S22:S1000<""))

Good, thank you, that works:
=SUMPRODUCT(SUBTOTAL(9,INDIRECT("q" & ROW(3:100000)))*(S3:S100000<""))

(There are 44,442 rows of data presently, so 1000 was too small.) :-)

Now please tell my why that works.

This also works:

=SUMPRODUCT(SUBTOTAL(109,INDIRECT("q" & ROW(3:44442))),*(TblPriorYrs[Managed]<""))
or
=SUMPRODUCT(SUBTOTAL(109,INDIRECT("q" & ROW(3:44442))),--(TblPriorYrs[Managed]<""))

Still trying to find another way around the ugly INDICRECT
statement. I can't even set a named value as equal to 44442 and
have it work. Ugh.

Much obliged, Claus.

/sh

Still trying to find another way around the ugly INDICRECT
statement. ...

This may be off-topic, but but you might consider something like
=SUMIF(S3:S100000,"<",Q3:Q100000)

In , zvkmpw
wrote:

Still trying to find another way around the ugly INDICRECT
statement. ...

This may be off-topic, but but you might consider something like
=SUMIF(S3:S100000,"<",Q3:Q100000)

Not off-topic, but I don't see how to apply the SUBTOTAL filtering
to it.

/sh