CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaspreadsheet
Max Di Campo
Max Di Campo

Reputation: 438

Spreadsheet get individual values and count

I have a list with dates, names and a value. I am now looking for hours for a solution to list all individual names on a specific date and count them

so the list looks like:

Date    Name    Value
14.06.2021  Frank   1
14.06.2021  Frank   1
14.06.2021  Michael 1
14.06.2021  John    1
14.06.2021  Michael 1
15.06.2021  Frank   1
15.06.2021  Michael 1
15.06.2021  Michael 1
15.06.2021  Frank   1
15.06.2021  John    1

As Output for 14.06.2021 I want to get:

Frank   2
Michael 2
John    1

I tried with index, but don't get this working. Anybody an idea?

Here is a spreadsheet with the data: https://docs.google.com/spreadsheets/d/1YremoikrEcvTHMifVbF1CYv6yyDn_fKz_5z43SPauic/edit?usp=sharing

Upvotes: 0

Views: 59

Answers (2)

Aresvik
Aresvik

Reputation: 4630

Try this...

For count:

=query({A:C};"select Col2,count(Col3) where Col1 = date '"&text(E2;"yyyy-mm-dd")&"' group by Col2 order by Col2 label count(Col3) '' ";0)

It gets the date from a cell (E2), but you can hard code it into the query if you need to, using:

where Col1 = date '2021-06-14'

For a sum of:

=query({A:C};"select Col2,sum(Col3) where Col1 = date '"&text(E2;"yyyy-mm-dd")&"' group by Col2 order by Col2 label sum(Col3) '' ";0)

enter image description here

Upvotes: 1

JPV
JPV

Reputation: 27302

In I5 I entered

=query(A2:C; "Select B, sum(C) where A = date '"&TEXT(I1; "yyyy-mm-dd")&"' group by B order by sum(C) desc label sum(C)''"; 0)

The formula references the date in cell I1. If you want to have the date part hard coded in the formula you can try

=query(A2:C; "Select B, sum(C) where A = date '2021-06-14' group by B order by sum(C) desc label sum(C)''"; 0)

Upvotes: 1

Related Questions