Using ARRAYFORMULA and QUERY to total numbers related to Sheet A to Sheet B

Question

I'm currently trying to create a formula that I can use for my Google Sheets Project. I Have two sheets (Sheet A, and Sheet B) Details on each sheet are the following:

Sheet A

A         |       B
Users       Total Number Count
User 1           2
User 2
User 3

Sheet B

A         |       B
Users       Total Number Count
User 1           2
User 2           4
User 3           1
User 3           2
User 3           4
User 3           1

I want to Tally the numbers on total number count from Sheet B and display them on the corresponding users on Sheet A

I am working with the following formula on Sheet A — Column B:

QUERY(Payments, "SELECT sum(B) WHERE A ='" & A2 & "' label sum(B) 'Total Number Count'" ,)

At the moment, it only displays a single value, I would like it to repeat for every row by using ARRAYFORMULA, but have read that they do not work together. Is there a workaround for this?

Answer 1

There are a number of ways to do this. Here, I will share a simple approach and will assume that your sheet names are Sheet A and Sheet B per your post. You will need to replace those with your actual sheet names if they differ.

Clear 'Sheet A'!B:B entirely (including the header). Then place the following formula in 'Sheet A'!B1.

=ArrayFormula({"Total Count";IF(A2:A="",,SUMIF('Sheet B'!A:A,A2:A,'Sheet B'!B:B))})

This formula will return the B1 header and all column results. You can change the header text within the formula itself. (You can't type directly into any cell of Col B with this formula in place, because it "owns" the entire column, as it should.)