Using a data table to replace time values with points values.

Welcome to the forum.

Where do you want these values? Which data does the formula need to look at?

Where have you manually mocked up some examples?

Bit of a needle in a haystack ...

1. Change the values in column A to match data... exactly.

2. Dump the merged cells in row 8.

3. This works in my version of Excel... but may fail in yours. We'll see. In B8, copied across:

=IF(B$2="Hrs",SUM(IFERROR(INDEX(Data!$B$2:$AO$6,MATCH(Demo!$A3:$A7,Data!$A$2:$A$6,0),MATCH((B$3:B$7+C$3:C$7/60)/24,ROUND(1440*Data!$B$1:$AO$1,0)/1440,0)),0)),"")

You seem to be using an older version of Excel than me. So, please refer to the attached file. If the formulae are enclosed within a pair of { }, these are array formulae.

These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

No idea how Glenn managed to work out what was required - he must have psychic powers that I don't possess.

This is actually very worrying. I have seen this before. It LOOKs like it's working... it returns a number, not an error... but the number is derived only from the first time value it sees. I had a vague memory in the back of my head, that this might happen, hence my comment about it possibly not working for you.

I wonder how many bridges have collapsed, or companies gone bust because of this aberrant behaviour. All I have to do now is remember HOW to fix it!!

Please try

=SUMPRODUCT(INDEX(Data!$A$2:$AO$6,N(INDEX({1;2;3;4;5},)),N(INDEX(MATCH(TIME(B3:B7,C3:C7,),INDEX(MROUND(N(+Data!$A$1:$AO$1),"0:15"),)),))))
Normal Enter

or

=SUM(INDEX(Data!$A$2:$AO$6,N(IF(1,{1;2;3;4;5})),N(IF(1,MATCH(TIME(B3:B7,C3:C7,),MROUND(N(+Data!$A$1:$AO$1),"0:15"))))))
Ctrl+Shift+Enter

Thanks, Bo_Ry for replying to my plea for help!! That's lovely... but not the solution that I only dimly recall. But, who cares!!

No-one can diagnose the invisible.... Please post a sample sheet showing what you did and what the formula is not doing.

Last try

=SUMPRODUCT(--(TIME(B3:B7,C3:C7,)=MROUND(+Data!$B$1:$AO$1,"0:1")),Data!$B$2:$AO$6)

The workbook is uploaded for a reason.

1.png

Bo_Ry... I've been looking at this formula Why this bit:

N(IF(1,{1;2;3;4;5}))

Doesn't it always resolve to {1;2;3;4;5}?? Trying to learn myself...

For Excel without Spill array, Index(row, column) can't read an array of row and column directly

https://excelxor.com/2014/09/05/inde...ray-of-values/

Evaluate these formulas (F9) and see what is inside this formula

=INDEX({1;2;3;4;5},{1;2;3;4;5}))) => ={1}

=INDEX({1;2;3;4;5},N(IF(1,{1;2;3;4;5}))) => ={1;2;3;4;5}

Thank you! All is clear, now...

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.