Find unique count using query function in google sheets

Question

I have 3 columns: date, user & total with multiple rows for the same date. I am using the query function to derive date wise total and unique count of the names but i am not able to count the unique occurence of values in user column.

Please refer to the attached google sheet and image of the sheet for sample data and a better understanding of my question.

Thanks for looking.

https://docs.google.com/spreadsheets/d/1FeNhOD_oCSOKGXAhQ0EVDqIUOgP3DRzZtMuUI6eHCuw/edit?usp=sharing

[Image of spreadsheet]

TheMaster · Accepted Answer · 2022-09-18 17:01:38Z

1

Use double query, first to group by and get unique and second to aggregate:

=QUERY(QUERY(A1:C15,"Select A,count(B),sum(C) group by A,B"),"Select Col1,count(Col2),sum(Col3) group by Col1",1)

answered Sep 18, 2022 at 17:01

TheMaster

51.6k7 gold badges76 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cooper · Accepted Answer · 2022-09-18 14:14:25Z

Try this:

function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const vs = sh.getRange(3,1,sh.getLastRow() - 2,3).getDisplayValues();
  const osh = ss.getSheetByName("Sheet1");
  osh.clearContents();
  let obj = {pA:[]};
  vs.forEach((r,i) => {
    if(!obj.hasOwnProperty(r[0])) {
      obj[r[0]] = {user:[r[1]],sum:Number(r[2])};
      obj.pA.push(r[0]);
    } else {
      obj[r[0]].user.push(r[1]);
      obj[r[0]].user = [...new Set(obj[r[0]].user)];
      obj[r[0]].sum += Number(r[2])
    }
  }); 
  let o = [["Date","sumTotal","unique user count"]];
  obj.pA.forEach(p => {
    o.push([p,obj[p].user.length,obj[p].sum]);
  });
  osh.getRange(1,1,o.length,o[0].length).setValues(o);
}

Data:

Date	User	Total
09/09/2022	Abc	100
09/09/2022	Hola1	100
09/09/2022	boygrey	100
09/09/2022	JJ11	100
09/09/2022	King	100
09/09/2022	Bite00	100
09/09/2022	Jacob22	100
09/09/2022	Abc	50
09/09/2022	Hola1	50
09/09/2022	boygrey	50
09/09/2022	JJ11	50
09/09/2022	BB12	50
09/09/2022	toyroom	50
10/09/2022	James	100

Output:

Date	unique user count	sumTotal
09/09/2022	9	1000
10/09/2022	1	100

Collectives™ on Stack Overflow

Find unique count using query function in google sheets

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related