Friday, November 23, 2012

yogi_Use Countif To Correctly Count Number Of Specified Entities In Cells With Data Validayogi_Use COUNTIF To Correctly Count Number Of Specified Entities In Cells With Data Validation (With Inadvertent Leading And Trailing Spaces)

                                          Google Spreadsheet   Post  #885
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Nov 23, 2012
user Veryblacklabel said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/Ub36TORgoxg)
Problems with formula Countif when used in a column with 'validation' in cells.
Hey,

I have problems using the formula Countif when I want to get results from cells with al list ('validation').
This seems to work in Microsoft Excel but not in Drive Sheet.
I would be thankful if someone could help me with this issue.

Greetings,
Johny

----------------------------------------------------------------------------------------
 following is a solution to the problem ... although ideally there should be no leading or trailing spaces in the DataValidation List, I have noticed people often enter the list with comma as the delimiter but also an inadvertent space. With the use of the TRIM function one gets the correct count even with the inadvertent leading and trailing spaces present