Conditional SUBTOTAL in XL210 Table

S

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
 
C

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

Sarah H.

Claus Busch said:
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
 
C

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

Sarah H.

Claus Busch said:
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
 
S

Sarah H.

Sarah H. <[email protected]> said:
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
 
Z

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)
 
S

Sarah H.

zvkmpw said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top