sumproduct and getpivot??

sfb

Board Regular
Joined
Apr 30, 2004
Messages
67
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
BCDEF
3DollarsSource
4SetNameChoiceDirectGrandTotal
5Set1SubsetAa79,181,31079,181,310
6b44,002,16344,002,163
7c10,892,05810,892,058
8d26,442,28326,442,283
9e16,740,52816,740,528
10f217,622,396217,622,396
11g49,555,34549,555,345
12h1,313,4741,313,474
13SubsetATotal445,749,557445,749,557
14SubsetBi171,855,197171,855,197
15j4,089,0884,089,088
16k20,922,05420,922,054
17g38,633,50738,633,507
18l8,809,9328,809,932
19SubsetBTotal244,309,779244,309,779
20SubsetCm345,764,654345,764,654
21SubsetCTotal345,764,654345,764,654
Sheet1
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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

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

Hope this helps
PGC
Book1
ABCDEFG
1
2
3
4Name
5SubsetASubsetA8
6SubsetB5
7SubsetC1
8
9
10
11
12
13SubsetATotal
14SubsetB
15
16
17
18
19SubsetBTotal
20SubsetC
21SubsetCTotal
22
23
24
Sheet3
 

sfb

Board Regular
Joined
Apr 30, 2004
Messages
67
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:
 

Forum statistics

Threads
1,136,366
Messages
5,675,355
Members
419,565
Latest member
Phil57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top