-1

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

0

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'")

1
  • Thank you, I didn't realize query could do that! I thought ArrayFormulas were the answer here as I wanted to perform a function on an array. But this works great! Thank you. – Brandon Keith Biggs Oct 9 '17 at 16:20
0

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)
1
  • I added a spreadsheet that should clear things up. This answer just reflects what is in column A and doesn’t perform any summing of the scores in data. – Brandon Keith Biggs Oct 9 '17 at 12:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.