Home 
Search 
Today's Posts 
#1




Formula help
I have a text value in cells A11. Let's say that the values read across
the row: A1 = "Duck", B1 = "Duck", C1 = "Cat", D1 = "Duck" I want a formula in cell E1 that will return "No" in this case, meaning that the items are not all same. The bit that makes it too hard for me is that there may be blanks, and the blanks should not be counted. So: A1 = "Duck", B1 = "", C1 = "Duck", D1="Duck" should be a "Yes" in column E. Thanks very much for any help. Daniel 
#2




Formula help
Try a variation of this:
E1: =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&"")) That formula counts the number of nonblank, unique items. Does that help? *********** Regards, Ron "Daniel Bonallack" wrote: I have a text value in cells A11. Let's say that the values read across the row: A1 = "Duck", B1 = "Duck", C1 = "Cat", D1 = "Duck" I want a formula in cell E1 that will return "No" in this case, meaning that the items are not all same. The bit that makes it too hard for me is that there may be blanks, and the blanks should not be counted. So: A1 = "Duck", B1 = "", C1 = "Duck", D1="Duck" should be a "Yes" in column E. Thanks very much for any help. Daniel 
#3




Formula help
Hi Ron
Thanks for offering a solution, but I'm not sure how to apply it. Maybe my example wasn't clear, but I wasn't sure why you have A1:A10  I'm trying to test across the row A11, then copy that formula down to 12,000 rows. Hope that clarifies. Daniel "Ron Coderre" wrote: Try a variation of this: E1: =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&"")) That formula counts the number of nonblank, unique items. Does that help? *********** Regards, Ron "Daniel Bonallack" wrote: I have a text value in cells A11. Let's say that the values read across the row: A1 = "Duck", B1 = "Duck", C1 = "Cat", D1 = "Duck" I want a formula in cell E1 that will return "No" in this case, meaning that the items are not all same. The bit that makes it too hard for me is that there may be blanks, and the blanks should not be counted. So: A1 = "Duck", B1 = "", C1 = "Duck", D1="Duck" should be a "Yes" in column E. Thanks very much for any help. Daniel 
#4




Formula help
Um...OK..You want this:
E1: =SUMPRODUCT((A11<"")/COUNTIF(A11,A11&"")) Does that help? *********** Regards, Ron "Daniel Bonallack" wrote: Hi Ron Thanks for offering a solution, but I'm not sure how to apply it. Maybe my example wasn't clear, but I wasn't sure why you have A1:A10  I'm trying to test across the row A11, then copy that formula down to 12,000 rows. Hope that clarifies. Daniel "Ron Coderre" wrote: Try a variation of this: E1: =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&"")) That formula counts the number of nonblank, unique items. Does that help? *********** Regards, Ron "Daniel Bonallack" wrote: I have a text value in cells A11. Let's say that the values read across the row: A1 = "Duck", B1 = "Duck", C1 = "Cat", D1 = "Duck" I want a formula in cell E1 that will return "No" in this case, meaning that the items are not all same. The bit that makes it too hard for me is that there may be blanks, and the blanks should not be counted. So: A1 = "Duck", B1 = "", C1 = "Duck", D1="Duck" should be a "Yes" in column E. Thanks very much for any help. Daniel 
#5




Formula help
This works perfectly  sorry, I now see how I could have just made the column
to row adjustment myself. I confess, I don't really understand the formula, but I guess that doesn't matter  thanks very much for providing a solution. Daniel "Ron Coderre" wrote: Um...OK..You want this: E1: =SUMPRODUCT((A11<"")/COUNTIF(A11,A11&"")) Does that help? *********** Regards, Ron "Daniel Bonallack" wrote: Hi Ron Thanks for offering a solution, but I'm not sure how to apply it. Maybe my example wasn't clear, but I wasn't sure why you have A1:A10  I'm trying to test across the row A11, then copy that formula down to 12,000 rows. Hope that clarifies. Daniel "Ron Coderre" wrote: Try a variation of this: E1: =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&"")) That formula counts the number of nonblank, unique items. Does that help? *********** Regards, Ron "Daniel Bonallack" wrote: I have a text value in cells A11. Let's say that the values read across the row: A1 = "Duck", B1 = "Duck", C1 = "Cat", D1 = "Duck" I want a formula in cell E1 that will return "No" in this case, meaning that the items are not all same. The bit that makes it too hard for me is that there may be blanks, and the blanks should not be counted. So: A1 = "Duck", B1 = "", C1 = "Duck", D1="Duck" should be a "Yes" in column E. Thanks very much for any help. Daniel 
#6




Formula help
See if this helps with the understanding:
Using this data: A1: DUCK B1: DUCK C1: (blank) D1: CAT and the formula =SUMPRODUCT((A11<"")/COUNTIF(A11,A11&"")) The (A11<"") part tests if each cell in A11 is not blank. Each nonblank returns a 1, each blank returns a zero. (Actually, it returns TRUE and FALSE, but Excel converts them to 1's and 0's) Consequently, the example data would return this array: 1,1,0,1 Next, the COUNTIF(A11,A11&"") section counts how many times it finds each cell's value in the range A11. Our data would return the array: 2,2,1,1 So the resulting array divisions would match the items from the first array with the items in the second array to get: 1/2, 1/2, 0/2, 1/1 The SUMPRODUCT function adds all of those values and is only really necessary in this example to force Excel to perform array calculations. In this case, the total equals 2 unique items. You could skip the SUMPRODUCT function and use this formula instead: =SUM((A11<"")/COUNTIF(A11,A11&"")) ....but you'd need to confirm that array formula by holding down the [Ctrl] and [Shift] keys when you press [Enter]. For many people, that's too obscure a combination to remember only occaissionally. Hence, I generally prefer the SUMPRODUCT function approach. I hope that helps? *********** Regards, Ron "Daniel Bonallack" wrote: This works perfectly  sorry, I now see how I could have just made the column to row adjustment myself. I confess, I don't really understand the formula, but I guess that doesn't matter  thanks very much for providing a solution. Daniel "Ron Coderre" wrote: Um...OK..You want this: E1: =SUMPRODUCT((A11<"")/COUNTIF(A11,A11&"")) Does that help? *********** Regards, Ron "Daniel Bonallack" wrote: Hi Ron Thanks for offering a solution, but I'm not sure how to apply it. Maybe my example wasn't clear, but I wasn't sure why you have A1:A10  I'm trying to test across the row A11, then copy that formula down to 12,000 rows. Hope that clarifies. Daniel "Ron Coderre" wrote: Try a variation of this: E1: =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&"")) That formula counts the number of nonblank, unique items. Does that help? *********** Regards, Ron "Daniel Bonallack" wrote: I have a text value in cells A11. Let's say that the values read across the row: A1 = "Duck", B1 = "Duck", C1 = "Cat", D1 = "Duck" I want a formula in cell E1 that will return "No" in this case, meaning that the items are not all same. The bit that makes it too hard for me is that there may be blanks, and the blanks should not be counted. So: A1 = "Duck", B1 = "", C1 = "Duck", D1="Duck" should be a "Yes" in column E. Thanks very much for any help. Daniel 
#7




Formula help
That's really clever  thanks for taking the time to explain.
As you've probably guessed, the actual example I'm applying this to isn't about ducks and cats. My problem is that it's not actually A1, B1, C1, and D1 that I'm testing, but rather G1, M1, S1 and Z1. Can the same formula work across this range (to put the answer in AA1)? Thanks again  if you've moved on to help others and feel this thread is at close, I'll quite understand Daniel "Ron Coderre" wrote: See if this helps with the understanding: Using this data: A1: DUCK B1: DUCK C1: (blank) D1: CAT and the formula =SUMPRODUCT((A11<"")/COUNTIF(A11,A11&"")) The (A11<"") part tests if each cell in A11 is not blank. Each nonblank returns a 1, each blank returns a zero. (Actually, it returns TRUE and FALSE, but Excel converts them to 1's and 0's) Consequently, the example data would return this array: 1,1,0,1 Next, the COUNTIF(A11,A11&"") section counts how many times it finds each cell's value in the range A11. Our data would return the array: 2,2,1,1 So the resulting array divisions would match the items from the first array with the items in the second array to get: 1/2, 1/2, 0/2, 1/1 The SUMPRODUCT function adds all of those values and is only really necessary in this example to force Excel to perform array calculations. In this case, the total equals 2 unique items. You could skip the SUMPRODUCT function and use this formula instead: =SUM((A11<"")/COUNTIF(A11,A11&"")) ...but you'd need to confirm that array formula by holding down the [Ctrl] and [Shift] keys when you press [Enter]. For many people, that's too obscure a combination to remember only occaissionally. Hence, I generally prefer the SUMPRODUCT function approach. I hope that helps? *********** Regards, Ron "Daniel Bonallack" wrote: This works perfectly  sorry, I now see how I could have just made the column to row adjustment myself. I confess, I don't really understand the formula, but I guess that doesn't matter  thanks very much for providing a solution. Daniel "Ron Coderre" wrote: Um...OK..You want this: E1: =SUMPRODUCT((A11<"")/COUNTIF(A11,A11&"")) Does that help? *********** Regards, Ron "Daniel Bonallack" wrote: Hi Ron Thanks for offering a solution, but I'm not sure how to apply it. Maybe my example wasn't clear, but I wasn't sure why you have A1:A10  I'm trying to test across the row A11, then copy that formula down to 12,000 rows. Hope that clarifies. Daniel "Ron Coderre" wrote: Try a variation of this: E1: =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&"")) That formula counts the number of nonblank, unique items. Does that help? *********** Regards, Ron "Daniel Bonallack" wrote: I have a text value in cells A11. Let's say that the values read across the row: A1 = "Duck", B1 = "Duck", C1 = "Cat", D1 = "Duck" I want a formula in cell E1 that will return "No" in this case, meaning that the items are not all same. The bit that makes it too hard for me is that there may be blanks, and the blanks should not be counted. So: A1 = "Duck", B1 = "", C1 = "Duck", D1="Duck" should be a "Yes" in column E. Thanks very much for any help. Daniel 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
IF formula?  Excel Worksheet Functions  
writing a formula for a colored value  New Users to Excel  
referencing named formula using INDIRECT function  Excel Worksheet Functions  
Simplify formula  Excel Worksheet Functions  
Match / Vlookup within an Array formula  Excel Discussion (Misc queries) 