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 is blank, it should not be shown. Is there a way to do this? What I’ve been doing is copying and pasting and using formulas. However, this is not practical because ideally, I would like to view the information directly from the Pivot Table.

 

 

Thanks in advance.

Formulas and Functions

4 Replies

Sort By
    • 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