# Doing COUNTIFS equivalent in ArcGIS Field Calculator?

I have an attribute table with location data for a series of individual animals. They are often found at the same locations, so I have created a column in my attribute table where I list the rock ID. The Rock Id counts are repeated for each animal in order to keep data collection simple in the field, so rock 1 for animal 1 is not the same as rock 1 for animal 2. (In any instance where two animals used the same rock - I combined the rock id numbering system for their rocks, but this was rare.) I would like to get a count for the number of times an animal is found at each rock, so I can use symbology on my map that varies in size based on this attribute. (More times an animal uses a rock, larger the symbol).

I have a data set that looks something like this and I want to populate rock count to look something like what I have below

I know I can use CountIFS in excel - and then potentially import this in, but Ideally I could calculate this for the data already in ArcGIS using code and the field calculator. The summarize tool works well for telling me the unique totals with two case fields- but I would like to replicate this value in each cell. The output of that table is smaller than my original, since obviously there are some repeats.

I don't think I can do a join from this summary table, because each possible joining attribute (Rock_ID, Animal_ID) has more than one row in both tables.

You can use python with Collections.Counter library to Count unique combinations of Animal_ID & Rock_ID:

A Counter is a dict subclass for counting hashable objects.

It is possible to do this using Field Calculator but I recommend using cursors instead. Adjust input fc and field names and execute in Python Window:

``````import arcpy
from collections import Counter

fc = 'animals' #In the example screenshot a layer. Can also be a shapefile or fc in database, for example fc = r'C:\data.gdb\animals'

c = Counter()

#Store counts in dictionary
with arcpy.da.SearchCursor(fc,  ['Animal_ID','Rock_ID']) as cursor:
for row in cursor:
c[row]+=1

#Update count column
with arcpy.da.UpdateCursor(fc,['Animal_ID','Rock_ID','Rock_Count']) as cursor:
for row in cursor:
row[2] = c[tuple(row[:2])]
cursor.updateRow(row)
``````

Example in ArcGIS Pro, it is the same in ArcMap:

• Like the use of Counter() never used that before. Jun 3, 2019 at 8:56
• This is a better answer than mine! Jun 3, 2019 at 22:33
• @SonofaBeach your answer is also nice, can be done with standard Tools (=no python)
– BERA
Jun 4, 2019 at 4:46
• It took a bit of tweaking for this novice to run the code, but this did the trick, and was exactly why I turned to Stack - thank you!! I am impressed, and hope this can be helpful to others as well. Jun 5, 2019 at 3:04

You need a field that uniquely identifies each combination of animal and rock. Then you can use the Summary tool, and join the summary table back to the original table based on that unique field. Then you can calculate the count field based on the joined summary data:

• Create a new field, such as "Animal_Rock_ID" and calculate its value as a concatenation of 'Animal_ID' and 'Rock_ID' (with an underscore between the two, to make sure that a 11 and 1 as well as a 1 and 11 don't end up both being the same "111", but would be "11_1" and "1_11" instead). One way to do this in Python would be: `"{}_{}".format(!Animal_ID!, !Rock_ID!)`

• Run the summary tool.

• Add a join on your original feature class to the summary table, based on the 'Animal_Rock_ID' field in both.

• Calculate the field values in your original table from the joined summary field.

• Remove the join.

• Optionally, delete the 'Animal_Rock_ID' field (if you have no further or future use for it).

• This seems like it would work great - but I have foolishly stored these unique IDs for both Animal_ID and Rock_ID as integers. I tried using .str(!Animal_ID!) + '_' + .str(!Rock_ID!) to convert them to strings - but I keep getting an invalid syntax message - I'm new so I'm sure its simple, but a nudge in the right direction would be appreciated! Tysm Jun 5, 2019 at 2:49
• There are a few ways to do it. Removing the `.` from your `str()` may help. But I usually use the following: `"{}_{}".format(!Animal_ID!, !Rock_ID!)` . This uses variable interpolation to replace the `{}` pairs with the parameters in the `format()` function. Without using any of the many available options, it simply replaces them in the order that they appear. I have added this to the answer now. BTW, storing IDs as integers is good practice, especially if you need to do database searches on them. Integers are quickest for database searching, with or without database indexes. Jun 5, 2019 at 2:51
• Thanks for this clarification! I managed to get the column I was looking for with the code from @BERA - but I went ahead and created this column - as it may be useful for future analysis. This formatting did the trick - and I will definitely be using it more in the future. Also thanks for the affirmation on my choice to list the IDs as integers. Thank you! Jun 5, 2019 at 3:20