I have 2 google sheets I'm working off. The master and a copy to create a 'dashboard' for analytics.
Master Sheet
| name | quantity | price/quantity |
|---|---|---|
| RozMo | 10 | 1.75 |
| Tam | 3 | 3.65 |
| Gurba | 36 | 12 |
| Tam | 30 | 0.55 |
| RozMo | 25 | 0.75 |
| RozMo | 5 | 0.50 |
| RozMo | 2 | 0.35 |
| Gurba | 150 | 8.75 |
Dashboard Sheet - Desired Output
| name | quantity | price/quantity |
|---|---|---|
| RozMo | 42 | 0.939 |
| Tam | 33 | 0.831 |
| Gurba | 186 | 9.379 |
Dashboard Sheet - This is how far I've got
| name | quantity | price/quantity |
|---|---|---|
| RozMo | 42 | |
| Tam | 33 | |
| Gurba | 186 |
Formulae used
To get the unique names
=UNIQUE('Master Sheet'!$A$2:$A)
To get quantity
=SUMIFS('Master Sheet'!$B$2:$B,'Master Sheet'!$A$2:$A,A2)
How do I populate the third column?
CodePudding user response：
See how this works for you (I cannot test it, since you did not provide access to the spreadsheet):
=ArrayFormula(QUERY({'Master Sheet'!A2:C,'Master Sheet'!B2:B*'Master Sheet'!C2:C},"Select Col1, SUM(Col2), SUM(Col4)/SUM(Col2) WHERE Col1 Is Not Null GROUP BY Col1 LABEL Col1 'name', SUM(Col2) 'quantity', SUM(Col4)/SUM(Col2) 'price/qty' FORMAT SUM(Col4)/SUM(Col2) '0.000'"))
This one formula should produce all headers and results, formatted according to your full "desired result." If not, share a link to your spreadsheet (or a copy of it).