Home > Net >  Total Sum With Vlookup
Total Sum With Vlookup

Time:09-23

I have two spreadsheets with names and times. One is specific session times and on the second sheet, I want to sum up the total times based on each instance from sheet one.

Sheet 1: Session Time
Name   |   Time 
David        5
Mike         2
Daniel       3
David        2
Daniel       8


Sheet 2: Total Time (The one for which I need a forumula)

Name   |  Total Time
David        7
Mike         2
Daniel       11

I tried a countif and vlookup but I couldn't get it match more than one instance of the name on sheet 1. I also tried this suggested formual from a suggested post but its not summing a second instance of the user name

=ARRAYFORMULA(SUM(ifna(VLOOKUP(A2, 'Sheet 1'!A3:AA16, 5, false))))

A2 = David (On Sheet 2)

Sheet 1'!A3:AA16 = List of names in columns A and all the way to AA is a series of columns with numerical values

5 = the column number from with I want to return the sum for every instance of David (2 in this example with a total of 7)

False = its not sorted

Any help is mucho appriciado!

CodePudding user response:

You can use this formula in your Sheet2!B1:

={"Total Time";arrayformula(if(A2:A<>"",sumif(Sheet1!A1:A,A2:A,Sheet1!B1:B),""))}

Or simply use =arrayformula(if(A2:A<>"",sumif(Sheet1!A1:A,A2:A,Sheet1!B1:B),"")) in Sheet2!B2

  • Get the sum of the range based on a specific criteria using enter image description here

    enter image description here

    CodePudding user response:

    This can be accomplished with a simple QUERY, e.g.,

    =QUERY('Session Time'!A:B, "Select A, SUM(B) WHERE A Is Not Null GROUP BY A LABEL SUM(B) 'Total Time'")

  • Related