All - I'm back with another question. I couldn't find reference to this anywhere on the board, but if you know of a good post, please let me know.
Here's what I'm trying to do. Based on the pivot table below, I want to get a count of the number of Choices in each Subset. This pivot table is linked to an OLAP cube and doesn't allow me to pull the Choice field into the data area (where I could use Count). So, I'm left with only being able to do it externally.
I started with:
=SUMPRODUCT(--($C$5:$C$1107=$C5),--($D$5:$D$1107<>0))
But, of course, that gives me 1 because it only counts D5. I'd like to have it count from D5 --> D12, then for Subset B D14 --> D18, etc. I thought maybe some combo of sumproduct and getpivot might work, but I'm not getting anywhere that way... Any other thoughts???
Many thanks in advance.
SFB
Here's what I'm trying to do. Based on the pivot table below, I want to get a count of the number of Choices in each Subset. This pivot table is linked to an OLAP cube and doesn't allow me to pull the Choice field into the data area (where I could use Count). So, I'm left with only being able to do it externally.
I started with:
=SUMPRODUCT(--($C$5:$C$1107=$C5),--($D$5:$D$1107<>0))
But, of course, that gives me 1 because it only counts D5. I'd like to have it count from D5 --> D12, then for Subset B D14 --> D18, etc. I thought maybe some combo of sumproduct and getpivot might work, but I'm not getting anywhere that way... Any other thoughts???
Many thanks in advance.
SFB
Book3 | |||||||
---|---|---|---|---|---|---|---|
B | C | D | E | F | |||
3 | Dollars | Source | |||||
4 | Set | Name | Choice | Direct | GrandTotal | ||
5 | Set1 | SubsetA | a | 79,181,310 | 79,181,310 | ||
6 | b | 44,002,163 | 44,002,163 | ||||
7 | c | 10,892,058 | 10,892,058 | ||||
8 | d | 26,442,283 | 26,442,283 | ||||
9 | e | 16,740,528 | 16,740,528 | ||||
10 | f | 217,622,396 | 217,622,396 | ||||
11 | g | 49,555,345 | 49,555,345 | ||||
12 | h | 1,313,474 | 1,313,474 | ||||
13 | SubsetATotal | 445,749,557 | 445,749,557 | ||||
14 | SubsetB | i | 171,855,197 | 171,855,197 | |||
15 | j | 4,089,088 | 4,089,088 | ||||
16 | k | 20,922,054 | 20,922,054 | ||||
17 | g | 38,633,507 | 38,633,507 | ||||
18 | l | 8,809,932 | 8,809,932 | ||||
19 | SubsetBTotal | 244,309,779 | 244,309,779 | ||||
20 | SubsetC | m | 345,764,654 | 345,764,654 | |||
21 | SubsetCTotal | 345,764,654 | 345,764,654 | ||||
Sheet1 |