Getting certain values from pivot table

[farrusete]

Hi,


I've just known about this site and It is great! Thank you for your work.


How could I get from other sheet's pivot table values with a certain text in a field?


I mean, i have a pivot table showing 2 columns:


Concept | Amount


I want to get to my dashboard SUM of amount for concepts containing word "(upsell)".


Also to get only minus signed values from amount column would be appreciated.


Thank you in advance

 

[Luke M]

Can you elaborate more on what your PivotTable looks like? At first, I was thinking you would need the GETPIVOTDATA function, but the latter part of your question sounds like you want to sum multiple items. Perhaps something like:

=SUMPRODUCT(--(ISNUMBER(SEARCH("(upsell),"A2:A10))),--(B2:B10<0),B2:B10)

 

[farrusete]

Thank you for your quick answer.


I have a pivot table which shows just "Concept" and "amount" taken from a big table which has:


Sales person

Start date (contract)

End date (contract)

Total amount


And several columns with formulas to distribute amounts between months.


The thing is that i would like to get with GETPIVOTDATA from the other sheet (the one with the pivot) just those amounts with text "upsell" in concept.


Thank you again

 

[Luke M]

To gather multiple values from a single PivotTable, I don't think GETPIVOTDATA will work for you. Any chance the SUMPRODUCT formula can be adapted? I wrote it with the assumption that Concepts (with the "upsell" text) was in column A, and the amounts were in column B.