1 Reply Latest reply on Sep 4, 2013 5:50 AM by Alex Kerin

    SUMIF

    David Vale

      Hi,

       

      There have been several discussions around SUMIF with multiple qualifiers, however, I haven't found one that quite suits what I want to achieve, it maybe because I just don't understand the answers I am reading.

       

      With reference to the attached data, I want to SUM the Casing Disposal [Units] only when the [Invoice Number] has a Call Out Charge. So for instance

       

      Casing Disposal [Units] on invoice 1327703 would be SUMed, but the Casing Disposal [Units] on invoice 1328221 would not.

       

      Can any wizzes out there help?

       

      Tanks,

      David.

        • 1. Re: SUMIF
          Alex Kerin

          You need to reshape your data to deal with the merged cells and multiple column dates, but that won't help you solve your issue.

           

          There are a number of ways to solve this. Most will revolve around checking to see if the description contains callout for an invoice, and then making that result available to all of the rows for that invoice.

           

          I have done that with a lookup here; order the product description so that any with callout appear first, check for callout in this line, then use lookup for each other row of the invoice to check the first line. As this depends on callout being the first line checked, we need a way to order product description. As there isn't a field that allows me to do that, I created an order field [Order Descriptions] ]that sets any description with callout to 0.

           

          Then using the advanced compute of the table calculations we can set what is considered first..

           

          This should get you started.