VLOOKUP + SUM FORMULA PLEASE

[gilquenanoviii]

Hello guys, as you can see in the picture shared above.
in Pivot table, same brand "APPL" is under different categories.
please help me with the formula to use so that I will be able to get the total sum for the brand "APPL"

i use vlookup and its only giving me total of the first Appl which is -450

thank you in advance!

 

[Alex Blakenburg]

Why don't you just either rearrange the pivot or create another pivot that totals the Brands ?

 

[gilquenanoviii]

Why don't you just either rearrange the pivot or create another pivot that totals the Brands ?

Hello, because there is a lot more categories that i need to look for using this pivot table. and I wanted to use just 1 formula to get it. instead of refilterring the pivot table many times..

 

[jdellasala]

You should be using GETPIVOTDATA so that the formula will maintain its references as it changes. Formulas with simple cell references will break as soon as the Pivot Table changes. Turn it on in OPTIONS -> Formula.

Once you turn it on and go to a cell outside of the PT and then start a formula by typing = and then pointing to a cell in the PT, you'll see a complex GETPIVOTDATA formula automatically generate. Don't freak out - they look horrible! However, if you look carefully at the formula you'll see clues as to how to adapt it to cell F8 as a reference rather than whatever was automatically generated.
The GETPIVOTDATA formula can be used to return values which can be used in formulas.
As for the example above, VLOOKUP will only return ONE value. You should be using SUMIFS (or SUMIF if not available - I forget if the IFS formulas are available in 2016! In the case of SUMIF(S), it would be fine to reference an entire column as in this:

Book1
	A	B	C	D	E
1	APPL	231		APPL	725
2	B	222
3	C	272
4	D	358
5	APPL	161
6	D	178
7	C	172
8	B	425
9	C	336
10	APPL	333
Sheet1

Cell Formulas
Range	Formula
E1	E1	=SUMIF(A1:A10,D1,B1:B10)

Hope that helps.

 

[Alex Blakenburg]

Hello, because there is a lot more categories that i need to look for using this pivot table. and I wanted to use just 1 formula to get it. instead of refilterring the pivot table many times..

Assuming by categories you mean AAPL, BAYKR, KYEBU etc then I would definitely be rearranging the table or creating a new one so that the brands total in the pivot and using as GETPIVOT as @jdellasala also suggested to get the data. It does not mean you have to apply any filtering.