Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional SUBTOTAL in XL210 Table
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional subtotal | Excel Discussion (Misc queries) | |||
Conditional SUBTOTAL | Excel Discussion (Misc queries) | |||
Conditional Subtotal in table | Excel Discussion (Misc queries) | |||
Conditional SUBTOTAL | Excel Discussion (Misc queries) | |||
Conditional Subtotal Macro | Excel Programming |