Sumproduct to grab only second value in set

[briankb64]

Hi All -

I have a table set up that is similar to below (though it expands for multiple years and has many other names on it). My goal is to set up a function that will only grab the second non-blank value for Brian.

As of now I have arrays set up for Date (Dates2), Time (Times), The actual data set C2:C4 in this instance (Array1), and the names from C1:Z1 (Names).

The formula I am plugging in consistently returns 0 =SUMPRODUCT((Rads=C$1)*(Dates2=$A2)*(Array1<>"")*(SMALL(TIMES,2)),Array1)

If I drop out the Small part =SUMPRODUCT((Rads=C$1)*(Dates2=$A2)*(Array1<>""),Array1) the formula works and returns 65. The goal is to only return 50. Can anyone help point me in the correct direction?

Date	Time	Brian
1/1/2019	12:00:00 AM
1/1/2019	1:00:00 AM	15
1/1/2019	2:00:00 AM	50

 

[briankb64]

Sorry! The array for names is actually Rads

 

[StephenCrump]

Welcome to the Forum!

Perhaps something like this:

Test.xlsx
	A	B	C	D	E
1	Date	Time	Brian	Sue	Mary
2	1/01/2019			7
3	1/01/2019		15	21	8
4	2/01/2019			33	6
5	1/01/2019			44	4
6	1/01/2019		50		2
7
8
9
10			Mary	Brian	Sue
11		Date	1/01/2019	1/01/2019	1/01/2019
12		Position	3	2	3
13		Result	2	50	44
Sheet1

Cell Formulas
Range	Formula
C13:E13	C13	=INDEX(XLOOKUP(C10,RADS,Array1),SMALL(IF(XLOOKUP(C10,RADS,Array1)*(Dates2=C11),SEQUENCE(ROWS(Array1))),C12))

Named Ranges
Name	Refers To	Cells
Array1	=Sheet1!$C$2:$E$6	C13:E13
Dates2	=Sheet1!$A$2:$A$6	C13:E13
RADS	=Sheet1!$C$1:$E$1	C13:E13

 

[briankb64]

Hi Stephen! Thank you for your reply but unfortunately I do not have the monthly version of Office 365 and the xlookup/sequence formulas will not be available to me until July.

 

[StephenCrump]

I do not have the monthly version of Office 365 and the xlookup/sequence formulas will not be available to me until July.

Ah! My mistake - wishful thinking.

Try C13: =INDEX(Array1,SMALL(IF(INDEX(Array1,,MATCH(C10,RADS,))*(Dates2=C11),ROW(Array1)-MIN(ROW(Array1))+1),C12),MATCH(C10,RADS,)) array-entered