I have a polygon feature class where each polygon represents distribution range of a species, and I have over 90 species here where each species representing a single polygon.The attribute table also contains several binary numeric fields that indicate species conservation status-- EN, CR, VU, NT, LC, etc (1 if the species is classified in a certain status 0 if not). I also have a 10*10k grid. I want to count number of polygons (species) overlapping with each grid cell. And, I want to sum um the 1s in conservation status fields for each grid cell to enumerate the number of species belonging to each conservation status in each grid cell (so that I can create species heat/hotpot maps).
Spatial join tool is the approach I tried, but, this only allows me to run a merge rule for a single field. I want to run "sum" merge rule for seven fields in the species distribution feature class. I could run each field separately for the same merge rule and create multiple feature classes through spatial join tool, and then join all these tables via "make query table" tool (via a very long SQL expression) or iterate join table tool to join all feature classes generated by Spatial join tool.
Is there a suitable tool that satisfy my need? I am not very savvy on python scripts and that's why I need to support with existing tools and the model builder. I am using Arcmap 10.6