Can GETPIVOTDATA be used for this?

[XLML]

Hi,

I would like to be able to use the getpivotdata function in Sheet 2 below based on 2 criteria, the "BusUnit" and "Account". Can this be done using some variation of GETPIVOTDATA? Example of Source Data (Sheet1) and Destination (Sheet 2) are below.

Thanks in advance,
XLML

PT2.xls
	A	B	C	D	E	F	G
1	BusUnit	Account	Amount		Sum of Amount
2	1000	60225	75		BusUnit	Account	Total
3	2020	60430	60		1000	60225	200
4	1000	60225	125		1000 Total		200
5	2020	60430	30		2020	60430	90
6					2020 Total		90
7					Grand Total		290
Sheet1

PT2.xls
	A	B	C	D
1	BusUnit	Account	GETPIVOTDATA
2	1000	60225
3	2020	60430
Sheet2

 

[NateO]

Hello, you'll either want to generate a P.T. report or use Suproduct().

Book1
	A	B	C	D	E	F	G
1	BusUnit	Account	Amount		SumofAmount	Account
2	1000	60225	75		BusUnit	60225	60430
3	2020	60430	60		1000	200
4	1000	60225	125		2020		90
5	2020	60430	30
6
7
8
9
10	BusUnit	Account	GETPIVOTDATA
11	1000	60225	200
12	2020	60430	90
Sheet8

 

[XLML]

Thanks Nate. I tried using sum product. I named the ranges on Sheet 1 - "BusUnit", "Account" and "Amount". The formula returned 0. Do you know why this is?

XLML

PT2.xls
	A	B	C	D
1	BusUnit	Account	Amount
2	1000	60225	75
3	2020	60430	60
4	1000	60225	125
5	2020	60430	30
Sheet1

PT2.xls
	A	B	C	D
1	BusUnit	Account	SumProduct
2	1000	60225	0
3	2020	60430	0
Sheet2

 

[NateO]

You are definitely missing a ) at the end of the call.

The ranges absolutely must be of the same size. Did you get it to work with simple range references?

 

[XLML]

Hi Nate, This seems weird. I have used SumProduct successfully in the past. As you can see below, I deleted the names and used simple ranges and it doesn't work. Also, Excel is giving me an error message when I put closed parentheses at the end of the formula.

Any ideas?
XLMLSCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT>

PT2.xls
	A	B	C	D
1	BusUnit	Account	SumProduct
2	1000	60225	0
3	2020	60430	0
Sheet2

 

[Tom Urtis]

And while you're at it Nate, tell us where we can score one of those cool Toronto Maple Leaf caps you be sportin'.

 

[NateO]

Hmmm, maybe the original data is text? Try copying a blank cell, select your original data, paste special -> add.

Tom, in Toronto. I'm sure there's some good online offerings out there. Thought it was odd, someone offered me $200 for my alternat. Leafs sweater the other day... Not sure that covers the cost though.

 

[Tom Urtis]

Thanks for the info...I will pick one up on line. The $200 seems steep (maybe it was Canadian dollars?); game-worn jerseys always command more than off the rack. It's the best time of year now, down-hill to the playoffs after the AllStar break !!

 

[XLML]

Thanks for all your help, Nate. I rekeyed the data on another spreadsheet and the Sumproduct formulal worked.

XLML

 

[NateO]

You are welcome.

Thanks for all your help, Nate. I rekeyed the data on another spreadsheet and the Sumproduct formulal worked.

Yikes! That sounds like a chunderfull process going forward...

I might experiment with the Paste Special Method I mentioned, text to columns and/or the clean() worksheet function to coerce this data into a more workable set.

Tom, nope, USD, and I agree, the real deals are pricey to get into, e.g.,

http://www.icejerseys.com/toronto_maple_leafs.php

Mine also has a MLG commemorative (we're moving) patch on it. Not sure what mine cost originally, my girlfriend bought it online from a Canadian retailer. Hasn't seen too much ice time in terms of game-worn, although it has seen the odd pool hall, bowling alley, etc...

Agreed, very excited for the remainder of the season!