0

I have two worksheets https://docs.google.com/spreadsheets/d/141IcRU2RE_WmBGK4bMOI-_zyz1-hwU1TTylfbBz2pIE/edit?usp=sharing

https://docs.google.com/spreadsheets/d/1kGJaIu6leOzeXWyAyfaZsllMib8b8esOuDWYwzlxyYo/edit?usp=sharing

I am trying to count the number of instances each person has in each tab of sheet 2 and then import that total to sheet 1. Im unsure which formulas I should be using of if this is even possible. I've included a small description on each tab of sheet 2.

If someone could help me understand what I should be using to accomplish this I'd be grateful

4
  • Have you figured it out yet? – marikamitsos Sep 18 '20 at 19:57
  • I have not yet. – Matt Burress Sep 18 '20 at 21:09
  • Have you tried any of the given solutions? Did any work for you? If not we should try finding out why. If yes, as per site guidelines when an answer addresses your question, you should accept it so others can benefit as well. Please remember that you can also contribute to this community that helps you. – marikamitsos Sep 19 '20 at 14:51
  • I tried late last night when I was very tired and I was unable to get it to yield results. I will try again in a little bit. – Matt Burress Sep 19 '20 at 16:42
0

You can try this :

=query(
flatten(arrayformula(SPLIT(IMPORTRANGE("1kGJaIu6leOzeXWyAyfaZsllMib8b8esOuDWYwzlxyYo","concerns!b2:b"),",",true,true))),
"SELECT Col1,Count(Col1) group by Col1 order by Count(Col1) desc"
)
1
  • That worked to return counts, I should have been a little more clear, in Testing Sheet 2 column A is a fixed column of names, I'd like to return the count of the two tabs in Testing 1 to match the name in Column A of Testing 2, if that makes sense – Matt Burress Sep 18 '20 at 9:34
0

You mentioned:

...in Testing Sheet 2 column A is a fixed column of names, I'd like to return the count of the two tabs in Testing 1 to match the name in Column A of Testing 2

Please use the following formula

=ArrayFormula(IFERROR(VLOOKUP(C2:C, 
              QUERY({ArrayFormula(TRIM(flatten(SPLIT(B2:B22,",",1,1))));ArrayFormula(TRIM(flatten(SPLIT(errors!A2:A22,",",1,1))))}, 
                    "select Col1, count(Col1) where Col1 is not null group by Col1 label count(Col1) 'Totals' "),2,0)))

In the formula I did not use IMPORTRANGE. It is very easy though. All you have to do is change the ranges to your needs like
from errors!A2:A22 to IMPORTRANGE("xxxxxx","errors!A2:A")

enter image description here

If still in need, please let us know.

Functions used:

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.