0

What is the formula that I need to enter in 'Sheet2' to Output this table from the data in 'Sheet1'?

I have (dummy) data of People in a Google Sheet tab ('Sheet1') that looks like this (Name, Email and a numerical value of either blank, or '1'):

Current INPUT DATA TABLE in 'Sheet1':

enter image description here

I want to create another table, in another tab ('Sheet2') that outputs JUST those People who have any non-blank Value against their name, along with the SUM of those values:

Desired OUTPUT DATA TABLE in 'Sheet2'

enter image description here

You'll notice that in Input data (first table) there are three different People (Steve, Rick and John).

Steve always had blank Values in column D, so he doesn't appear in the Output data table (the second table).

In the input table, Rick appeared three times, but only twice did he have data in the Value column (i.e. '1'). Therefore, he did appear in the Output data table with '2' in the Value column (i.e. the sum total of 1 + 1).

In John's case, he appeared in the Output table with the sum of '3' in the Value column.

What is the formula that I need to enter in 'Sheet2' to Output this table from the data in 'Sheet1'?

Any help would be great!

2 Answers 2

0

I solved it with a query:

=query(Sheet1!A2:D,"select A,B,C, SUM(D) where D is not null group by A,B,C label sum(D) '' ",  0 )

Here a test sheet

Query

2
  • that's awesome! Thank you so much! :D If I wanted to sort descending by the Value column (i.e. see the highest numbers first) how can I amend the formula to display that?
    – RoCrow
    Mar 22 at 15:25
  • This is the same formula with a descending order: =query(Sheet1!A2:D,"select A,B,C, SUM(D) where D is not null group by A,B,C order by SUM(D) desc label sum(D) ''", 0 )
    – Daniele
    Mar 22 at 16:39
0

You can do this directly with a Pivot Table:

enter image description here

I've shown my solution here on the same sheet, but you can refer to the source data table from another sheet.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.