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?
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 |