Search code examples
google-sheetspivotgoogle-sheets-formulaarray-formulasgoogle-sheets-query

Search for a text combination and then moving its next 2 cells to the other table

Alright, I have 3 tables, from the first, the value of 2 cells is taken and brought in the 3rd table and from the 3rd table the sum of those cells is added in one cell and counted in another. Ex: First table Jacob 1 Apple (written 2 times in 2 different rows). This will go in the 3rd table just the same then numbers will be counted (2 numbers) and written in the 2nd first cell and also added and written in another cell. I want to redo this as many times I add a new name or number it kind of breaks the formulas and the formulas start stealing from the above cell or from the below.

Link: https://docs.google.com/spreadsheets/d/1a0NIeA2xlDzpK-C4Mvq1UANfYdUHc9gHli6U3v2L6r8/edit?usp=sharing

Solution

  • delete everything in range E:K
    paste this formula into E1 cell:

    =ARRAYFORMULA({QUERY(A1:C, 
 "select A,count(A),sum(B) 
  where A is not null 
  group by A 
  label count(A)'DS events total',sum(B)'Overall attendees'", 0),
 QUERY(IFERROR(QUERY(A1:C, 
 "select sum(B) 
  where A is not null 
  group by A 
  pivot C", 0)*1, 
 {"Overal BTs", "Overal CTs", "Overal FTs", "Overal PTs"}), 
 "select Col4,Col1,Col3,Col2", 1)})

    0