CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
aName
aName

Reputation: 257

Google sheets dynamically build an array from another

Say I have:

enter image description here

But on a separate worksheet I would like to generate a new table of the following data of totals:

enter image description here

I am stumped as to how to have this using as few formulas as possible - I don't want to copy paste the same formula among the rows, because my data could be any number of rows long

Additionally, what if there was another column called "timestamp" (of the action), would we be able t do things like generating a table of data that is only within a range of dates?

Does anyone have the secret sauce?

I have gotten close, where I can use ARRAYFORMULA to extend the calculation for one column for every row, but I can't figure out how to have this formula work for every column.

A prepared spreadsheet and my attempt is here: https://docs.google.com/spreadsheets/d/1cbLafPF-ajNSVMid8MCBNDSWstvDuMJe89Q1eAtvW4Q/edit?usp=sharing

I encourage you to have an attempt (the cells with the formulas in 'my attempt' I have colored orange)

Upvotes: 0

Views: 190

Answers (1)

marikamitsos
marikamitsos

Reputation: 10573

It is a MAP, QUERY pivot combination that you can use

=MAP(QUERY(A131:B149,"Select B,count(B) where A<>'' 
                      group by B pivot A",1), 
       LAMBDA(toZero, IF(toZero="", 0, toZero)))

(Do adjust the formula according to your ranges and locale)

enter image description here

Upvotes: 2

Related Questions