In Google Sheets, how do I output data and it's sum in one sheet, taken from only certain filtered/duplicate/conditional data from a different sheet?

Question

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':

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'

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!

Answer 1

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

Answer 2

You can do this directly with a Pivot Table:

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