Hi,
Hope someone can help.
I am trying to create a formula to lookup data in an input sheet and create a summary of key info and gap analysis.
the table looks simply as follows:
[TABLE="width: 300"]
<tbody>[TR]
[TD][TABLE="width: 270"]
<tbody>[TR]
[TD="colspan: 4"]Input Table[/TD]
[/TR]
[TR]
[TD]REF[/TD]
[TD]Region[/TD]
[TD]PM[/TD]
[TD]Wave[/TD]
[/TR]
[TR]
[TD]Ref 1[/TD]
[TD]Region 1[/TD]
[TD]PM1[/TD]
[TD]Wave 1[/TD]
[/TR]
[TR]
[TD]Ref 2[/TD]
[TD]Region 1[/TD]
[TD][/TD]
[TD]Wave 1[/TD]
[/TR]
[TR]
[TD]Ref 3[/TD]
[TD]Region 2[/TD]
[TD]PM3[/TD]
[TD]Wave 2[/TD]
[/TR]
[TR]
[TD]Ref 4[/TD]
[TD]Region 2[/TD]
[TD]PM4[/TD]
[TD]Wave 1[/TD]
[/TR]
[TR]
[TD]Ref 5[/TD]
[TD]Region 3[/TD]
[TD]PM5[/TD]
[TD]Wave 3[/TD]
[/TR]
[TR]
[TD]Ref 6[/TD]
[TD]Region 3[/TD]
[TD]PM1[/TD]
[TD]Wave 2[/TD]
[/TR]
[TR]
[TD]Ref 7[/TD]
[TD]Region 4[/TD]
[TD]PM2[/TD]
[TD]Wave 3[/TD]
[/TR]
[TR]
[TD]Ref 8[/TD]
[TD]Region 4[/TD]
[TD]PM1[/TD]
[TD]Wave 1[/TD]
[/TR]
[TR]
[TD]Ref 9[/TD]
[TD]Region 5[/TD]
[TD]PM4[/TD]
[TD]Wave 1[/TD]
[/TR]
[TR]
[TD]Ref 10[/TD]
[TD]Region 5[/TD]
[TD]PM5[/TD]
[TD]Wave 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 250"]
<tbody>[TR]
[TD][TABLE="width: 169"]
<tbody>[TR]
[TD="colspan: 2"]Output table[/TD]
[/TR]
[TR]
[TD]Region[/TD]
[TD]No PM[/TD]
[/TR]
[TR]
[TD]Region 1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Region 2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Region 3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Region 4[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Region 5[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
the formula I am using is:
=COUNTIFS($C$30:$C$39,"<>",$D$30:$D$39,$H30,$E$30:$E$39,{"PM1","PM2","PM3","PM4","PM5"},$F$30:$F$39,"Wave 1")+COUNTIFS($C$30:$C$39,"<>",$D$30:$D$39,$H30,$E$30:$E$39,{"PM1","PM2","PM3","PM4","PM5"},$F$30:$F$39,"Wave 2")
Is there a way to combine all of the PMs into a table and count any person which is in that table/named range against the PM column in the input sheet? This means I can just add the name of a new PM to the table rather than having to alter the formula.
Thanks in advance for any help.
Hope someone can help.
I am trying to create a formula to lookup data in an input sheet and create a summary of key info and gap analysis.
the table looks simply as follows:
[TABLE="width: 300"]
<tbody>[TR]
[TD][TABLE="width: 270"]
<tbody>[TR]
[TD="colspan: 4"]Input Table[/TD]
[/TR]
[TR]
[TD]REF[/TD]
[TD]Region[/TD]
[TD]PM[/TD]
[TD]Wave[/TD]
[/TR]
[TR]
[TD]Ref 1[/TD]
[TD]Region 1[/TD]
[TD]PM1[/TD]
[TD]Wave 1[/TD]
[/TR]
[TR]
[TD]Ref 2[/TD]
[TD]Region 1[/TD]
[TD][/TD]
[TD]Wave 1[/TD]
[/TR]
[TR]
[TD]Ref 3[/TD]
[TD]Region 2[/TD]
[TD]PM3[/TD]
[TD]Wave 2[/TD]
[/TR]
[TR]
[TD]Ref 4[/TD]
[TD]Region 2[/TD]
[TD]PM4[/TD]
[TD]Wave 1[/TD]
[/TR]
[TR]
[TD]Ref 5[/TD]
[TD]Region 3[/TD]
[TD]PM5[/TD]
[TD]Wave 3[/TD]
[/TR]
[TR]
[TD]Ref 6[/TD]
[TD]Region 3[/TD]
[TD]PM1[/TD]
[TD]Wave 2[/TD]
[/TR]
[TR]
[TD]Ref 7[/TD]
[TD]Region 4[/TD]
[TD]PM2[/TD]
[TD]Wave 3[/TD]
[/TR]
[TR]
[TD]Ref 8[/TD]
[TD]Region 4[/TD]
[TD]PM1[/TD]
[TD]Wave 1[/TD]
[/TR]
[TR]
[TD]Ref 9[/TD]
[TD]Region 5[/TD]
[TD]PM4[/TD]
[TD]Wave 1[/TD]
[/TR]
[TR]
[TD]Ref 10[/TD]
[TD]Region 5[/TD]
[TD]PM5[/TD]
[TD]Wave 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 250"]
<tbody>[TR]
[TD][TABLE="width: 169"]
<tbody>[TR]
[TD="colspan: 2"]Output table[/TD]
[/TR]
[TR]
[TD]Region[/TD]
[TD]No PM[/TD]
[/TR]
[TR]
[TD]Region 1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Region 2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Region 3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Region 4[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Region 5[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
the formula I am using is:
=COUNTIFS($C$30:$C$39,"<>",$D$30:$D$39,$H30,$E$30:$E$39,{"PM1","PM2","PM3","PM4","PM5"},$F$30:$F$39,"Wave 1")+COUNTIFS($C$30:$C$39,"<>",$D$30:$D$39,$H30,$E$30:$E$39,{"PM1","PM2","PM3","PM4","PM5"},$F$30:$F$39,"Wave 2")
Is there a way to combine all of the PMs into a table and count any person which is in that table/named range against the PM column in the input sheet? This means I can just add the name of a new PM to the table rather than having to alter the formula.
Thanks in advance for any help.