2

I recently came across this spreadsheet in another question and made a copy. Here it is:

https://docs.google.com/spreadsheets/d/14pCL0x-hlFyzWoW6-F8H33C6gJuYBv-p3NsjSvA5A0I/edit?usp=sharing

What I want to do is skip column B using the array formula. So column B would be completely empty, column C will contain the totals, D unique, and E the other unique. Is it possible to do using array formula in Google Sheets?

player0
  • 124,011
  • 12
  • 67
  • 124
Some old man
  • 31
  • 1
  • 4

2 Answers2

2

sure, why not:

=ARRAYFORMULA(QUERY({F3:H, 
 IF((G3:G<>"")*(COUNTIFS(F3:F&G3:G, F3:F&G3:G, ROW(G3:G), "<="&ROW(G3:G))=1), F3:F&G3:G, ),
 IF((H3:H<>"")*(COUNTIFS(F3:F&H3:H, F3:F&H3:H, ROW(H3:H), "<="&ROW(H3:H))=1), F3:F&H3:H, )},
 "select Col1,' ',count(Col1),count(Col4),count(Col5) 
  where Col1 !='' 
  group by Col1 
  order by count(Col1) desc 
  label count(Col1)'Total',
        count(Col4)'Unique (for id)',
        count(Col5)'Unique (for name)',
        ' '''", 1))

0

spreadsheet demo

Community
  • 1
  • 1
player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    I see. I had assumed that when the poster said, *completely empty" they meant they wanted to be able to write manually@ in that column. – MattKing Feb 01 '20 at 15:14
0

No. It is not possible. Not the answer you want i'm sure, but you cannot "skip" columns or rows when generating content from a single cell.

There are other workarounds and methods to get at what you're going for, but you'll need to share a sample sheet and/or be more specific about what you're trying to do.

Good luck!

MattKing
  • 7,373
  • 8
  • 13