Forum Discussion
COUNTIFS with data validation lists
I tried this and still got #SPILL or it literally just spilled into the cells below.
https://docs.google.com/spreadsheets/d/1lpeRjZNX5t1P-fjxmbLZhm50mq-Orqwc/edit?usp=sharing&ouid=106366700738151245865&rtpof=true&sd=true (This is the file I'm working with - I've deleted tabs that aren't relevant).
You can see in this document we have 12 office tabs. Each one collects data on inspectors, timeliness of reports, and type of report. On the "Average Report Times" tab, I have a list of the inspectors and I need to collate the total reports each inspector has written for each field office.
On the TABLE DATA tab, in Column E, there is a list of Reports that are the ones we're looking to count.
In the example, of row 5, I need to be able to count how many reports they've written for the Atlanta office that match one of the names in the list on TABLE DATA E2:E71.
I hope that makes sense.
In R2:
=SUM(COUNTIFS(INDIRECT("'"&R$1&"'!A2:A1000"),$A2,INDIRECT("'"&R$1&"'!K2:K1000"),'TABLE DATA'!$E$2:$E$71))
Fill to the right, then down or vice versa)