Hello all,
I am adapting an array formula that I read about from this link (great resource by the way). The purpose is to count the number of unique items in a column based on specifed conditions, without using conditional formatting, filters, etc.
The range where I am setting the criteria is named dynamicCountry (or, $D$2:$D$596) and the range where the unique items are to be counted from is named dynamicData (or, $F$2:$F$596).
The one "twist" is that that although I am searching based on one single criteria, the criteria is spelled differently throughout the range. For example, if I am searching for "Canada", but it is sometimes presented as "Canada"or "Can.".
I have tried to rearrange the formula to account for these alternatives, but each way I have tried returns either 0 or #DIV/0.
I have tried with wildcards and using nested "AND" statements, such as:
{=SUM(IF(AND(dynamicCountry="Canada",dynamicCountry="Can.",1/(COUNTIFS(dynamicCountry,"Canada",dynamicCountry,"Can.",dynamicData,dynamicData))),0))}
{=SUM(IF(dynamicCountry="*can*",1/(COUNTIFS(dynamicCountry,"*can*",dynamicData,dynamicData)),0))}
Any ideas or suggestions would be much appreciated!
I am adapting an array formula that I read about from this link (great resource by the way). The purpose is to count the number of unique items in a column based on specifed conditions, without using conditional formatting, filters, etc.
The range where I am setting the criteria is named dynamicCountry (or, $D$2:$D$596) and the range where the unique items are to be counted from is named dynamicData (or, $F$2:$F$596).
The one "twist" is that that although I am searching based on one single criteria, the criteria is spelled differently throughout the range. For example, if I am searching for "Canada", but it is sometimes presented as "Canada"or "Can.".
I have tried to rearrange the formula to account for these alternatives, but each way I have tried returns either 0 or #DIV/0.
I have tried with wildcards and using nested "AND" statements, such as:
{=SUM(IF(AND(dynamicCountry="Canada",dynamicCountry="Can.",1/(COUNTIFS(dynamicCountry,"Canada",dynamicCountry,"Can.",dynamicData,dynamicData))),0))}
{=SUM(IF(dynamicCountry="*can*",1/(COUNTIFS(dynamicCountry,"*can*",dynamicData,dynamicData)),0))}
Any ideas or suggestions would be much appreciated!