Forum Discussion

enavarrobernal's avatar
enavarrobernal
Copper Contributor
Oct 10, 2024

How to add a conditional formula to a Pivot?

Hello all,   I have a Pivot Table where I track my inventory purchases and negative adjustments during the month. I need to see the results only when there is a purchase, so if the Purchase field i...
Formulas and Functions
enavarrobernal's avatar
enavarrobernal
Copper Contributor
Oct 23, 2024

Hello Lorenzo 

 

First, thank you for your help, and I apologize for the delayed reply. In your scenario, is there a way to create a filter for items that have purchase and negative adjustment values only?

 

Basically, what I need to know is which products were purchased this month and also adjusted this month.

 

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Oct 24, 2024

    Hello enavarrobernal 

     

    Below is an easy solution with a classic PivotTable where you'll have to filter [Item No.] on Calculated Field [Display Item] = 1 (as highlighted that column can be hidden): 

     

     

    Calculated Field:

    = AND( Purchase > 0, 'Negative Adjmt.' < 0 )

     

    This won't prevent you from filtering on specific [Item No.] if necessary

    Other options (no add. column) exist but depend on which version of Excel you run & on which platform (Windows, Mac...), info. you haven't provided so far

     

    Let me know how it goes with this...

Resources