5 Replies Latest reply: Feb 27, 2012 1:58 AM by Navin Gorapalli RSS

    IF Function Help

      I have the following data and I am trying to do a count of how many records have 1,2,3 or 4 products.

       

      For example data

      Customer NameProduct 1
      Product 2Product 3
      Product 4
      Cust 1Y
      YY
      Cust 2
      Y
      Y
      Cust 3YYY
      Cust 4Y
      YY
      Cust 5
      Y
      Y
      Cust 6Y
      YY

       

      So what I would like to do is write a formula that would count the # of customers that have either 1,2,3, or 4 products. So far I have tried the following fomula but can't seem to get it to work (I am still new to QV and just learning the ropes for IF functions).

       

      =If([Prod 1] = 'Y', [Prod 2] = 'Y', [Prod 3] = 'Y', [Prod 4] = 'Y'),Count(distinct([Customer_name])),' ')

       

      Am I using the right function?

      If so how should I be writing it to get the desired results? If no, what should I be using and how?

       

      Thanks

       

      Tim

        • Re: IF Function Help
          Celambarasan Adhimulam

          Hi,

               Try with this

              =RangeTextCount([Prod 1],[Prod 2],[Prod 3],[Prod 4])

               =RangeCount([Prod 1],[Prod 2],[Prod 3],[Prod 4])

          It will give you the number of products which has text in that.It won't consider null.

               =Sum(if(RangeCount([Prod 1],[Prod 2],[Prod 3],[Prod 4])>0,1))

           

          Celambarasan

          • Re: IF Function Help
            Gautam Parab

            Hi, please try the following expression


            =count(DISTINCT if([Product 1]='Y' OR [Product 2]='Y' OR  [Product 3]='Y' OR  [Product 4]='Y', [Customer Name]))

             

            • Re: IF Function Help

              Hi,

                  See the attached sample and refer to the expression written in the text box which counts the records having more than one product.

               

                  Hope this helps you,let me know if you have any queries.

               

              Regards

              Navin.G

                • IF Function Help

                  Navin,

                   

                  I see what you are doing here and I think that it is what I am looking for but I have one question: How do you make it scalable for 14,000+ customers.

                   

                  I see what you did in this example with the following code but that is only 6 Customers and I need to make this scalable to grow for no matter how many customers I have in the future.

                   

                  =IF(Count({$<Customer={'Cust 1'}>}If([Product 1]='Y',1))+

                  Count({$<Customer={'Cust 1'}>}if([Product 2]='Y',1))+

                  Count({$<Customer={'Cust 1'}>}If([Product 3]='Y',1))+

                  Count({$<Customer={'Cust 1'}>}If([Product 4]='Y',1))>=1,1,0)

                    +

                  IF(Count({$<Customer={'Cust 2'}>}If([Product 1]='Y',1))+

                  Count({$<Customer={'Cust 2'}>}if([Product 2]='Y',1))+

                  Count({$<Customer={'Cust 2'}>}If([Product 3]='Y',1))+

                  Count({$<Customer={'Cust 2'}>}If([Product 4]='Y',1))>=1,1,0)

                    +

                  IF(Count({$<Customer={'Cust 3'}>}If([Product 1]='Y',1))+

                  Count({$<Customer={'Cust 3'}>}if([Product 2]='Y',1))+

                  Count({$<Customer={'Cust 3'}>}If([Product 3]='Y',1))+

                  Count({$<Customer={'Cust 3'}>}If([Product 4]='Y',1))>=1,1,0) 

                  +

                  IF(Count({$<Customer={'Cust 4'}>}If([Product 1]='Y',1))+

                  Count({$<Customer={'Cust 4'}>}if([Product 2]='Y',1))+

                  Count({$<Customer={'Cust 4'}>}If([Product 3]='Y',1))+

                  Count({$<Customer={'Cust 4'}>}If([Product 4]='Y',1))>=1,1,0)

                  +

                  IF(Count({$<Customer={'Cust 5'}>}If([Product 1]='Y',1))+

                  Count({$<Customer={'Cust 5'}>}if([Product 2]='Y',1))+

                  Count({$<Customer={'Cust 5'}>}If([Product 3]='Y',1))+

                  Count({$<Customer={'Cust 5'}>}If([Product 4]='Y',1))>=1,1,0)

                    +

                    IF(Count({$<Customer={'Cust 6'}>}If([Product 1]='Y',1))+

                  Count({$<Customer={'Cust 6'}>}if([Product 2]='Y',1))+

                  Count({$<Customer={'Cust 6'}>}If([Product 3]='Y',1))+

                  Count({$<Customer={'Cust 6'}>}If([Product 4]='Y',1))>=1,1,0)

                    • Re: IF Function Help

                      Hi,

                          I think I have solved you problem man...!! I am attaching the sample file here.Please refer to the macro

                          which is written on 'ONRELOAD'

                       

                          I have assumed that the product columns are the same and do not change like there will be only 4 types of

                          products.

                       

                         Please let me know if this solves your issue.

                       

                      Regards

                      Navin.G