Array functions and If and Exact all combined not working

Question

I am a little new to this, so some help would be much apprecaited.&nbsp;I have a spreadsheet with names, projects and a 1 or 0 depending on if they are working on it for the week in question on one sheet.&nbsp;IEName, &nbsp; Project, Week1, Week2, Week3 ...Dave, &nbsp; &nbsp; P1, &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0Tim, &nbsp; &nbsp; &nbsp; P1, &nbsp; &nbsp; &nbsp; &nbsp; 0, &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 1Dave, &nbsp;&nbsp; P3, &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp;Sarah, &nbsp; P4 &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 1Tim, &nbsp; &nbsp;&nbsp; P5, &nbsp; &nbsp; &nbsp;&nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 0&nbsp;I want to create a new sheet with a summary for project management and team planning purposes, where each of the names has a 1 or 0 if they are busy on ANY project.IE&nbsp;Name, Week1, Week2, Week3, ...Dave, &nbsp;&nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1Tim &nbsp; &nbsp; &nbsp;&nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1Sarah &nbsp; &nbsp;&nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 1&nbsp;I have{=if(EXACT($B$8,'Resource Allocation'!B8:'Resource Allocation'!B114) AND'Resource Allocation'!G8:'Resource Allocation'!G114&gt;0 ,1,0)}Where $B$8 is the name and the&nbsp;'Resource Allocation'!B8:'Resource Allocation'!B114 is the range of the names on the main sheet and the other range is the 1/0 if they are busyDoesn't work.... what am I doing wrong? I cannot seem to get over this bit at all.ThanksIvan&nbsp;

jkpieterse · Answer

I think you can use the COUNTIFS function here.

Suppose your data is in 'Resource Allocation'$A$1:$E$100 and your new table starts in A1 then this formula should do the trick:

=1*(COUNTIFS('Resource Allocation'!C$2:C$100,1,'Resource Allocation'!$A$2:$A$100,$A2)>0)

Forum Discussion

Array functions and If and Exact all combined not working

1 Reply

Resources