Forum Discussion

Gemma Telfer's avatar
Gemma Telfer
Copper Contributor
May 19, 2020

Formua help: Count IF

Hi All

 

I am wondering if anyone can help please? I am trying to use data we gather on support we provide but cannot seem to get forumlas that I know work. At the moment we have to go through 200 entries a month by hand to tally the numbers by eye. I know there must be a better way but cannot figure it out.

 

The two main problems I have are:

1) the address coloum is populated by calls we receive and despite being told advisors still put in two towns. For example if there is a larger town nearby people quite often put that in their address as well as their actual town. From what I have tried if I do a countif fofmula for Fauldhouse it also includes those which also have in Addiewell, Fauldhouse.

Realistically I need it to search and count the word Fauldhouse and exclude any which also have Addiwell in the same cell. Is this possible?

 

2) My second problem is again people entering multiple names for the same building. In my example the same building is called Fauldhouse GP Surgery, Fauldhouse Centre and 111 any street, fauldhouse. Is there a way to make it check for all options and still count them if one of the correct names appears?

 

Many thanks for any help can be provided, it will be much appreciated.

excel
Formulas and Functions

3 Replies

  • mathetes's avatar
    mathetes
    Gold Contributor
    May 19, 2020

    Gemma Telfer 

     

    The solution, assuming this is an ongoing issue, is to change the way you collect the raw data in the first place. It's a mistake, anyway, to have the entire address (street PLUS city PLUS mail code)  in one cell. And as you've discovered, you can't "train people" to do it correctly.

     

    Specifically, since it would appear that you use town or city name as the basis for your subtotals, make that a cell of its own, and use data validation (Data....Validation...) to restrict the possible entries to a single name. See the attached.

    • Gemma Telfer's avatar
      Gemma Telfer
      Copper Contributor
      May 19, 2020

      mathetes Thnakyou for getting back to me. 

       

      I know the best solution would be to change how we gather the info, The info is gathered on a serperate system and exported in this fashion so we are not able to change that. I will just have to stick to manually counting and keep asking for the way we gather to be changed. 

       

      Thank you 🙂

Resources