Hello Excel friends,
I'm trying to sum a multiple array lookup without much success. I've read through many threads on this forum and googled furiously, but just can't quite seem to make it work.
I have a table of lookup values, I'm showing 4 pairs here, which represent start & end dates. In reality the table of lookup values might have 20 or 50+ pairs (rows) of values.
A1:B4
<tbody>
</tbody>
These are used to perform searches on a date table (which I've named WorkdayArray, with WorkdayArrayDateCol as [Date], and WorkdayArrayColMon-Fri as [Monday] to [Friday]).
<tbody>
</tbody>
This lookup serves to sum up the number of each workday in the range of the task.
At the moment I'm using:
=INDEX(WorkdayArrayMonCol,MATCH(A1,WorkdayArrayDateCol)):INDEX(WorkdayArrayFriCol,MATCH(B1,WorkdayArrayDateCol))
To perform the search which returns a 2d array. In this example the range specified (17/7/2017-31/07/2017) returns an array, which if I SUM it returns 11.
I then multiply the result of that first lookup with an availability entry, which is a 5-value array itself, representing whether they work a full day or not. In this example, the staff member works half days on Monday & Friday.
(Values in P3:P7)
<tbody>
</tbody>
In this example, when I multiply the two:
=INDEX(WorkdayArrayMonCol,MATCH(A1,WorkdayArrayDateCol)):INDEX(WorkdayArrayFriCol,MATCH(B1,WorkdayArrayDateCol))*TRANSPOSE(P3:P7)
= 8.5
What I need is the sum from multiple lookups all in one shot so I can get the total, in this case, 19, in one lookup.
<tbody>
</tbody>
I thought =SUM(INDEX(WorkdayArrayMonCol,MATCH(A1:A4,WorkdayArrayDateCol)):INDEX(WorkdayArrayFriCol,MATCH(B1:B4,WorkdayArrayDateCol))*TRANSPOSE($P$3:$P$7)) would be a winner but no such luck.
I've also tried:
<tbody>
</tbody>
The closest I can ever seem to manage is an array with the 4 resulting totals in it like so:
<tbody>
</tbody>
..and that always seems to be the end of the line, I can't manage to SUM up to 19. As per the title I need to perform this summation in one cell.
Thank you very much for your help, I can't seem to quite get it there!
I'm trying to sum a multiple array lookup without much success. I've read through many threads on this forum and googled furiously, but just can't quite seem to make it work.
I have a table of lookup values, I'm showing 4 pairs here, which represent start & end dates. In reality the table of lookup values might have 20 or 50+ pairs (rows) of values.
A1:B4
17/07/2017 | 31/07/2017 |
20/07/2017 | 26/07/2017 |
27/07/2017 | 31/07/2017 |
24/07/2017 | 31/07/2017 |
<tbody>
</tbody>
These are used to perform searches on a date table (which I've named WorkdayArray, with WorkdayArrayDateCol as [Date], and WorkdayArrayColMon-Fri as [Monday] to [Friday]).
Date | Monday | Tuesday | Wednesday | Thursday | Friday |
11/07/2017 | 0 | 1 | 0 | 0 | 0 |
12/07/2017 | 0 | 0 | 1 | 0 | 0 |
13/07/2017 | 0 | 0 | 0 | 1 | 0 |
14/07/2017 | 0 | 0 | 0 | 0 | 1 |
17/07/2017 | 1 | 0 | 0 | 0 | 0 |
18/07/2017 | 0 | 1 | 0 | 0 | 0 |
19/07/2017 | 0 | 0 | 1 | 0 | 0 |
20/07/2017 | 0 | 0 | 0 | 1 | 0 |
21/07/2017 | 0 | 0 | 0 | 0 | 1 |
24/07/2017 | 1 | 0 | 0 | 0 | 0 |
25/07/2017 | 0 | 1 | 0 | 0 | 0 |
26/07/2017 | 0 | 0 | 1 | 0 | 0 |
27/07/2017 | 0 | 0 | 0 | 1 | 0 |
28/07/2017 | 0 | 0 | 0 | 0 | 1 |
31/07/2017 | 1 | 0 | 0 | 0 | 0 |
<tbody>
</tbody>
This lookup serves to sum up the number of each workday in the range of the task.
At the moment I'm using:
=INDEX(WorkdayArrayMonCol,MATCH(A1,WorkdayArrayDateCol)):INDEX(WorkdayArrayFriCol,MATCH(B1,WorkdayArrayDateCol))
To perform the search which returns a 2d array. In this example the range specified (17/7/2017-31/07/2017) returns an array, which if I SUM it returns 11.
I then multiply the result of that first lookup with an availability entry, which is a 5-value array itself, representing whether they work a full day or not. In this example, the staff member works half days on Monday & Friday.
(Values in P3:P7)
Monday | 0.5 |
Tuesdays | 1.0 |
Wednesdays | 1.0 |
Thursdays | 1.0 |
Fridays | 0.5 |
Total Availability | 0.80 |
<tbody>
</tbody>
In this example, when I multiply the two:
=INDEX(WorkdayArrayMonCol,MATCH(A1,WorkdayArrayDateCol)):INDEX(WorkdayArrayFriCol,MATCH(B1,WorkdayArrayDateCol))*TRANSPOSE(P3:P7)
= 8.5
What I need is the sum from multiple lookups all in one shot so I can get the total, in this case, 19, in one lookup.
=SUM(INDEX(WorkdayArrayMonCol,MATCH(A1,WorkdayArrayDateCol)):INDEX(WorkdayArrayFriCol,MATCH(B1,WorkdayArrayDateCol))*TRANSPOSE(P3:P7)) | =8.5 |
=SUM(INDEX(WorkdayArrayMonCol,MATCH(A2,WorkdayArrayDateCol)):INDEX(WorkdayArrayFriCol,MATCH(B2,WorkdayArrayDateCol))*TRANSPOSE(P3:P7)) | =4 |
=SUM(INDEX(WorkdayArrayMonCol,MATCH(A3,WorkdayArrayDateCol)):INDEX(WorkdayArrayFriCol,MATCH(B3,WorkdayArrayDateCol))*TRANSPOSE(P3:P7)) | =2 |
=SUM(INDEX(WorkdayArrayMonCol,MATCH(A4,WorkdayArrayDateCol)):INDEX(WorkdayArrayFriCol,MATCH(B4,WorkdayArrayDateCol))*TRANSPOSE(P3:P7)) | =4.5 |
Total: | =19 |
<tbody>
</tbody>
I thought =SUM(INDEX(WorkdayArrayMonCol,MATCH(A1:A4,WorkdayArrayDateCol)):INDEX(WorkdayArrayFriCol,MATCH(B1:B4,WorkdayArrayDateCol))*TRANSPOSE($P$3:$P$7)) would be a winner but no such luck.
I've also tried:
=IFERROR(MMULT(MMULT(TRANSPOSE(ROW(INDEX(WorkdayArrayMonCol,MATCH(A1:A4,WorkdayArrayDateCol)):INDEX(WorkdayArrayFriCol,MATCH(B1:B4,WorkdayArrayDateCol)))^0),INDEX(WorkdayArrayMonCol,MATCH(A1:A4,WorkdayArrayDateCol)):INDEX(WorkdayArrayFriCol,MATCH(B1:B4,WorkdayArrayDateCol))),$P$3:$P$7),0) =SUMPRODUCT((H2:H38>=A1)*(H2:H38<=B1)*I2:M38*TRANSPOSE(P3:P7)) (only good for one entry) Even breaking it down into vectors and adding them to see if that made a difference: =SUM(INDEX(WorkdayArrayMonCol,MATCH(A1:A4,WorkdayArrayDateCol)):INDEX(WorkdayArrayMonCol,MATCH(B1:B4,WorkdayArrayDateCol))*$P$3 +INDEX(WorkdayArrayTueCol,MATCH(A1:A4,WorkdayArrayDateCol)):INDEX(WorkdayArrayTueCol,MATCH(B1:B4,WorkdayArrayDateCol))*$P$4 +INDEX(WorkdayArrayWedCol,MATCH(A1:A4,WorkdayArrayDateCol)):INDEX(WorkdayArrayWedCol,MATCH(B1:B4,WorkdayArrayDateCol))*$P$5 +INDEX(WorkdayArrayThuCol,MATCH(A1:A4,WorkdayArrayDateCol)):INDEX(WorkdayArrayThuCol,MATCH(B1:B4,WorkdayArrayDateCol))*$P$6 +INDEX(WorkdayArrayFriCol,MATCH(A1:A4,WorkdayArrayDateCol)):INDEX(WorkdayArrayFriCol,MATCH(B1:B4,WorkdayArrayDateCol))*$P$7) & NETWORKDAYS.INTL but that doesn't seem to work with ranges at all. |
<tbody>
</tbody>
The closest I can ever seem to manage is an array with the 4 resulting totals in it like so:
8.5 |
4 |
2 |
4.5 |
<tbody>
</tbody>
..and that always seems to be the end of the line, I can't manage to SUM up to 19. As per the title I need to perform this summation in one cell.
Thank you very much for your help, I can't seem to quite get it there!