Jun 17 2022 06:38 AM
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:
Some notes:
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!
Jun 17 2022 02:32 PM
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.