help with extracting multiple array data

[brend0n]

I have a project with a number of resources and different task to complete over a extended period.
the maximum number of task assigned to an individual at one time is 7.

I was planning to use a sum product which reference a drop down list of the resources,then task no, then date then tell me which number that relates to
=sumproduct(resource drop down list = A2:A25)*(task number = B2:B25)*(date=C1:AA1)*(C2:AA25)
and additional table would use an if statement to use put the actual task in place of the numberbut i get a #value error if i drop the (c2:AA25) it will give me a 1 but wont give me a 0 is there is no task there.

ie: CV, task 2, 4/9/17 should give me a 0 but i get a 1

Resource	TASK	04-Sep-17	05-Sep-17	06-Sep-17	07-Sep-17	08-Sep-17	09-Sep-17	10-Sep-17	11-Sep-17	12-Sep-17	13-Sep-17	14-Sep-17	15-Sep-17	16-Sep-17	17-Sep-17	18-Sep-17	19-Sep-17	20-Sep-17	21-Sep-17	22-Sep-17	23-Sep-17	24-Sep-17	25-Sep-17	26-Sep-17	27-Sep-17	28-Sep-17	29-Sep-17	30-Sep-17	01-Oct-17	02-Oct-17	03-Oct-17	04-Oct-17	05-Oct-17	06-Oct-17	07-Oct-17	08-Oct-17	09-Oct-17	10-Oct-17	11-Oct-17	12-Oct-17	13-Oct-17	14-Oct-17	15-Oct-17	16-Oct-17	17-Oct-17	18-Oct-17	19-Oct-17	20-Oct-17	21-Oct-17
CV	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
CV	2																																												2	2	2	2	2
CV	3																																	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3
CV	4
CV	5
CV	6
CV	7
SS	1																																												2	2	2	2	2
SS	2																																	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3
DL	1	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16
DL	2																																	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3
DL	3
DL	4
DL	5
DL	6
AG	1																																	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3
AG	2
AG	3	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18	18
JC	1	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19
GB	1	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19	19
BW	1	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23
BW	2	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
BW	3	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16	16
BW	4																																	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3

<tbody>
</tbody>

 

[Peter_SSs]

How does it go if you add the missing parentheses?

=sumproduct((resource drop down list = A2:A25)*(task number = B2:B25)*(date=C1:AA1)*(C2:AA25))

 

[brend0n]

Hi Peter thanks for the quick reply.
unfortunately that didn't help.
including the C2:AA25 I get the #value error. If I remove this part (keeping the missing parentheses) I can only get a 1 not 0 using the same test oultined in the OP (ie: CV, task 2, 4/9/17 should give me a 0 but i get a 1)

 

[brend0n]

For the data part (C2:AA25) givign me a #Value , would it be something as simple as i need to change the cells with now values to a zero

 

[Peter_SSs]

.. would it be something as simple as i need to change the cells with now values to a zero

I'm now guessing that you don't actally have cells with no value, but probably cells with a formula that returns a text value of "" or similar). If so, there are at least a couple of choices.

a) Change the existing formula in C2:AA25 to return 0 instead of "" (and you could use Excel's settings for the worksheet, or Custom Format the range, to hide those zero values if you want), or

b) Leave the "" values and try
=SUMPRODUCT((B28= A2:A25)*(B29= B2:B25)*(B30=C1:AA1)*IF(ISNUMBER(C2:AA25),C2:AA25,0))

 

[brend0n]

option b worked thank you so much Peter

The formula I ended up using was:
=SUMPRODUCT((CONCATENATE($G$73,$M$82)='calendar_task no.'!$C$2:$C$58)*(B$75='calendar_task no.'!$D$1:$LG$1)*('calendar_task no.'!$D$2:$LG$58))

G73 = Resource
M82 = Task name
B75 = Date
D2:LG58 = Data

 

[Peter_SSs]

option b worked thank you so much Peter

The formula I ended up using was:
=SUMPRODUCT((CONCATENATE($G$73,$M$82)='calendar_task no.'!$C$2:$C$58)*(B$75='calendar_task no.'!$D$1:$LG$1)*('calendar_task no.'!$D$2:$LG$58))

G73 = Resource
M82 = Task name
B75 = Date
D2:LG58 = Data

Hmm, well that doesn't seem to have anything to do with whether the 'main data' ('calendar_task no.'!$D$2:$LG$58) is numbers or text, which seemed to be a sticking point with the original sample data.

But, hey, if it works for you that is all that matters I guess.

 

[brend0n]

yeah, I think the main data might have had something to do with it, as this formula didn't work till i performed a find replace on the "" to 0