2 Replies Latest reply on May 9, 2016 1:15 PM by Carolyn Marks

    Create a category using IF statements but on a subset of data yet show total $

    Carolyn Marks

      Hello--

      I'm connecting to an Access Database.  My data looks like the following (a lot more of it, though.  Hopefully you won't need a packaged workbook but can give me hints how to do this.  My problem ends up being the total sales column.  Rather than total sales, I get a duplicate RMS sales and then everything in the "No Trade Credit Spend" Category, which isn't what I want. 

       

      Customer     $                         LOB

      123               50000                 Trade Credit

      123               15000                 Supply

      123               250000               Trade Credit

      124               10000                 S&MS

      124               50000                 Compliance

      124               150000               Trade Credit

      125               50000                  Supply

       

      My desired output is:

                                                             Number of Customers          Total Sales          Trade Credit Sales

      Trade Credit Spend >$250K              1                                              $315000                         $300000 (notice, now adding in the Supply for the Total Sales)

      Trade Credit Spend >$100K              1                                               $210000                        $150000 (notice, now adding in Compliance & S&MS for Total Sales)

      Trade Credit Spend>$0

      No Trade Credit Spend                      1                                             $50000                              0