Hello,
I am using the COUNTIF function in one Excel workbook (Workbook A) to count the number of times certain account names appear within specific ranges of another Excel workbook's (Workbook B's) various sheets. I have the addresses of Workbook B's sheets (the ones that contain the values to be counted) in a table in Workbook A as shown below. Next to that table is another table containing the values (which are account names) to be counted ("Values to Count" column), and in the other column I'm hoping to have the number of occurrences of each value ("Count" column). For now, the "Count" column shows the formula that I thought would work to count the values within the ranges (right now these formulas are returning the "#VALUE!" error)...
To explain the formula currently in the "Count" column:
- Knowing that I was using the contents of other cells as the range addresses, I used the INDIRECT function to start the "range" part of the COUNTIF function.
- Since I needed to use the COUNTIF function on multiple ranges, I joined the addresses in the "Range Addresses" table together using the TEXTJOIN function with commas (",") as the delimiter. I figured this would make sense since, when you select multiple ranges, Excel separates their addresses with a comma.
- I typed "true" to have the TEXTJOIN function ignore blank addresses if I were to have any.
- I selected the entire "Range Addresses" column to be joined together with the TEXTJOIN function.
- To finish the COUNTIF function, I told the function to count the number of occurrences of the values in the "Values to Count" column.
Some notes:
- I cannot select the ranges themselves for the "range" part of the COUNTIF formulas, I have to pull them from the "Range Addresses" table.
- The addresses in the "Range Addresses" table are not hard-coded - they are formulas that pull the file path, name, and sheet names from other cells and join them together using "&."
- The addresses in the "Range Addresses" table work for other formulas on their own, but I can't get them to work as a group in formulas (probably because I'm not using the INDIRECT function correctly).
- I have Workbook B open when I'm trying to use this count function and have made Workbook B editable, so I don't see why these formulas wouldn't be returning anything unless I'm just using the functions or references incorrectly.
I would very much appreciate help on this. The jist of what I'm doing is, as a way to check my work, I am counting the number of transactions for specific accounts in a ledger file that has multiple sheets.
Thank you!
1) You don't need TEXTJOIN. For such model
formula could be
=SUM( COUNTIF( INDIRECT( Table1[Range addresses] ), [@[Values to count]] ) )COUNTIF returns array of counts for each range which we sum to receive total counts.
2) Please note, INDIRECT doesn't work if referenced file is closed, you shall keep General Ledger opened.