Conditional SUBTOTAL in XL210 Table

[Sarah H.]

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

 

[Claus Busch]

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.

 

[Sarah H.]

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

 

[Claus Busch]

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.

 

[Sarah H.]

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


Row(3:100000) is only a counter.

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

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

 

[Sarah H.]

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

 

[zvkmpw]

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)

 

[Sarah H.]

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