3 Replies Latest reply on Sep 12, 2018 3:42 PM by Hari Ankem

    Need to count field based on condition in another field

    Anastasia Mondy

      What I am trying to do is get a count of each ethnicity. I have a field for target communities and each row has a string of ethnicity data. I want to pick out every row that has a specific ethnicity and count them. My problem is that my "project number" field causes some rows to be duplicates because they are the same project just in different locations which is messing up my count.

       

      For example, if I wanted to count all the times the word Asian was used I would create the calculation:

           if contains([Break Down of Target Communites], "Asian") then 1 else 0 end

       

      Or if I wanted to count the number of distinct project numbers I would create the calculation:

           Countd(Project Number)

       

      But I can't figure out how to tell the calculation to only recognize the unique project numbers and then calculate the number of specific ethnic communities from only that set of data. Is this possible or should I be coming at this from a different angle?