How to use relative references in an ARRAYFORMULA with DSUM

Question

I have a formula: Total Hours:

=DSUM(data!A1:B, "Number of Hours", {"Provider Name";A2})

The column A is a dynamic list of all the providers. How do I use an array formula to fill in all the rows that have a provider in column A, using the provider that is in that row in column A?

I tried doing: Test 1:

=ARRAYFORMULA(IF(A2:A<>"", DSUM(data!$A$1:$B, "Number of Hours", {"Provider Name";A2}), ""))

But the A cell does not change. I get the array down the column like I wanted, but it is all for the provider in A2. I also tried: Test2:

=ARRAYFORMULA(IF(A2:A<>"", DSUM(data!$A$1:$B, "Number of Hours", {"Provider Name";A2:A}), ""))

But this gives me a sum of all the provider hours in every cell in the column, not just the provider in that row. I also tried: Test3:

=ARRAYFORMULA(IF(A2:A<>"", DSUM(data!$A$1:$B, "Number of Hours", {"Provider Name";INDIRECT(ADDRESS(ROW(),1, 4))}), ""))

But it just does the provider in A2. What else should I try? I do not want to copy all the rows every time a new person comes into or leaves column A. Here is an example spreadsheet:

https://docs.google.com/spreadsheets/d/1aUQ5WHjsvgGU_nZtNLcPTilJDRlOz-Z3prfNbgrJNzw/edit?usp=sharing

Answer 1

It's not possible to use DSUM in the way described in the question. Instead of DSUM use QUERY:

=QUERY(data!A:B,"select A,SUM(B) where A <>'' group by A label SUM(B) 'Total Hours'")

Answer 2

How do I use an array formula to fill in all the rows that have a provider in column A, using the provider that is in that row in column A?

Try:

=ArrayFormula(A1:A)