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