Count values in another sheet in Google Spreadsheet where sheet name is a cell value using ARRAYFORMULA

user_3383907 0
Viewed 501

I have a Google Spreadsheet with multiple sheets. I cannot share a sample because this is G-Suite Enterprise and it is locked. If absolutely needed I can create a dummy one on my personal Gmail.

One of the sheets has a list of lookup values and sheet names. I am trying to come up with an ARRAYFORMULA that will count the # of times Lookup Value shows in column A of Lookup Sheet Name.

| Lookup Value | Lookup Sheet Name | Count |
|--------------|-------------------|-------|
| one          | Primary           | ...   |
| two          | Secondary         | ...   |
| three        | Stuff             | ...   |
| ...          | Primary           | ...   |
| ...          | ...               | ...   |

I came up with this formula For C2 but it does not work. I don't exactly know why but it shows 1 for every lookup value or just one row with 1.

=ArrayFormula(IF(A2:A <> "", COUNTIF(INDIRECT(B2:B & "!A2:A"), A2:A), ))

I do not want to use Google Apps Script to solve this.

google-sheets
1 Answers

Here is one possible solution that combines the values of the different sheets in one by adding a second column with constant value (the name of the seet for example).

=ArrayFormula(
  IF(A:A<>"",
    COUNTIFS(
      {
        FILTER('sheet A'!A:A,NOT(ISBLANK('sheet A'!A:A)));
        FILTER('sheet B'!A:A,NOT(ISBLANK('sheet B'!A:A)))
      },"="&A:A,
      {
        FILTER(IF(ISBLANK('sheet A'!A:A),,"sheet A"),NOT(ISBLANK('sheet A'!A:A)));
        FILTER(IF(ISBLANK('sheet B'!A:A),,"sheet B"),NOT(ISBLANK('sheet B'!A:A)))
      },"="&B:B
    ),
  )
)

enter image description here

user_17454320
Related