sumproduct and getpivot??

[sfb]

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

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

 

[pgc01]

Hi sfb

This is one solution to build a table with all the groups and respective count of items

1 - Get the list of subset names (with autofilter) and copy them somewhere

2 - The number of items of each subset is the difference in rows between the cell with the subset name and the cell with the subset name plus " Total"

I did a small example with the data you posted. We are only interested in column C.

In F5

=MATCH(E5&" Total",$C:$C,0)-MATCH(E5,$C:$C,0)

Hope this helps
PGC

Book1
	A	B	C	D	E	F	G
1
2
3
4			Name
5			SubsetA		SubsetA	8
6					SubsetB	5
7					SubsetC	1
8
9
10
11
12
13			SubsetATotal
14			SubsetB
15
16
17
18
19			SubsetBTotal
20			SubsetC
21			SubsetCTotal
22
23
24
Sheet3

 

[sfb]

A-ha! Good idea! I think that will definitely work for what I need. Excellent!

Thank you!

I'm out tomorrow (and just happened to log in this evening), so I'll give it a whirl when I'm back in the office on Wed. But, at first glance and with a quick try, I think that will get me exactly what I need.

THANK YOU!!!

SFB :wink:

 

[sfb]

PGC -

YOU ROCK!

Thank you!

SFB