Forum Discussion
COUNTIFS with data validation lists
I can't seem to find anything that actually solves my issue.
I have a spreadsheet with a data tab that has a list of services. On other individual tabs, I have a column where they can select from that list of services.
I was using the formula to count the number of reports on the specific market tabs based on the individual doing said reports: =COUNTIFS(Atlanta!$A:$A,$A6,Atlanta!$K:$K,"*Report*")
We are now needing to count the total number of services rather than just services with report in the name. When I use the formula to include the list from the data tab, the program forces me to include an "@" and I get a #SPILL or 0.
The formula I used is: =COUNTIFS(Atlanta!$A:$A,$A3,Atlanta!$K:$K,@'TABLE DATA'!$E:$E)
4 Replies
It looks like you're trying to use the "@" symbol in your formula as a reference to a data validation list. However, the "@" symbol is not a valid way to reference a data validation list in a formula.
Instead, you can try using the INDIRECT function to create a reference to the data validation list. Here's an example formula:
=COUNTIFS(Atlanta!$A:$A,$A3,Atlanta!$K:$K,INDIRECT("'TABLE DATA'!$E:$E"))
This formula uses the INDIRECT function to create a reference to the data validation list in cell E:E of the "TABLE DATA" sheet. The COUNTIFS function then counts the number of rows in the Atlanta sheet where column A matches cell A3 and column K contains a value from the data validation list.
Make sure that the data validation list is in a separate sheet from the sheet where you're using the formula, as this can cause issues with circular references. Also, double-check that the range you're referencing with the INDIRECT function includes only the values in the data validation list, without any header or blank cells.
If it's not what you're looking for, add some examples to your results, as mentioned by Mr.Harun24HR .
I hope this helps!
I tried this and still got #SPILL or it literally just spilled into the cells below.
Work Product Tracker (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)
- Post few sample data then expected result.
