
Re: IF Function Help
Celambarasan Adhimulam Feb 24, 2012 1:27 AM (in response to Tim Doolittle)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 Feb 24, 2012 12:58 AM (in response to Tim Doolittle)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
Navin Gorapalli Feb 24, 2012 4:19 AM (in response to Tim Doolittle)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

Example Cust.qvw 137.0 K

IF Function Help
Tim Doolittle Feb 24, 2012 1:57 PM (in response to Navin Gorapalli )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
Navin Gorapalli Feb 27, 2012 1:58 AM (in response to Tim Doolittle)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

Example Cust.qvw 137.5 K


