Array formula - Count and Table functions

[Excel_pal]

Hello Excel Gurus:

Need your help on array function. I have two tables. Table A is the summary and Table B has the data. I would like three things to do in Table A with values feeding from Table B.

Status column in Table A: Based on value in column A (Project) of Table A, match it in column A of Table B and calculate how many Actions (column B) are ‘Done” and return in %. Ideally I would like to return as a sentence that reads X out of Y actions are Done.

Risk column in Table A: Based on value in column A (Project) of Table A, match it in column A of Table B and calculate the number of actions identified as R (column D, Table B). Even if it returns just the number of actions at risk is fine. Ideally I would like to read a sentence X action “Action 1”, “Action 2” are at risk.

Table A:

	A	B	C	D
1	Project	Status	% Done	Risk
2	Project A	2 out of 4 actions are Done	50%	1 action "Action 2" is at risk
3	Project B	1 out of 2 actions are Done	50%	0 action "" is at risk
4	Project C	1 out of 3 actions are Done	33%	1 action "Action 1" is at risk

<tbody>
</tbody>

Table B:

	A	B	C	D
1	Project	Action	Status	Risk (R,G,Y)
2	Project A	Action 1	Done	G
3	Project A	Action 2	In Progress	R
4	Project A	Action 3	In Progress	G
5	Project A	Action 4	Done	G
6	Project B	Action 1	To Do	G
7	Project B	Action 2	Done	G
8	Project C	Action 1	To Do	R
9	Project C	Action 2	In Progress	G
10	Project C	Action 3	Done	G

<tbody>
</tbody>

 

[Aladin Akyurek]

In B2 of Sheet2 enter and copy down:

=COUNTIFS(Sheet1!$A$2:$A$10,A2,Sheet1!$B$2:$B$10,"?*",Sheet1!$C$2:$C$10,"done")&" out of "&COUNTIFS(Sheet1!$A$2:$A$10,A2,Sheet1!$B$2:$B$10,"?*")&" actions are done"

In C2 of Sheet2 enter and copy down:

=COUNTIFS(Sheet1!$A$2:$A$10,A2,Sheet1!$B$2:$B$10,"?*",Sheet1!$C$2:$C$10,"done")/COUNTIFS(Sheet1!$A$2:$A$10,A2,Sheet1!$B$2:$B$10,"?*")

In D2 of Sheet2 control+shift+enter, not just enter, and copy down:

=COUNTIFS(Sheet1!$A$2:$A$10,A2,Sheet1!$B$2:$B$10,"?*",Sheet1!$D$2:$D$10,"R")&" action(s) "&TEXTJOIN(", ",TRUE,IF(Sheet1!$A$2:$A$10=$A2,IF(Sheet1!$D$2:$D$10="R",Sheet1!$B$2:$B$10,""),""))&" is/are at risk"

 

[Excel_pal]

Worked like a charm. Thanks so much.

Two other enhancements I would like to add:
1. When no actions are marked as 'done', the value should return 0%. Right now its showing blank.
2. If no risks are assigned to any project, it should read 'Risks not identified'. Right now it reads 0 actions(s) is/are at risk. This way it separates from projects that have skipped for assigning risks vs project that truly have no risk.

Thanks again.

 

[Aladin Akyurek]

Worked like a charm. Thanks so much.

You are welcome.

Two other enhancements I would like to add:
1. When no actions are marked as 'done', the value should return 0%. Right now its showing blank.

It does return 0, not blank. C2 should be formatted via Format Cells as Percentage.

2. If no risks are assigned to any project, it should read 'Risks not identified'. Right now it reads 0 actions(s) is/are at risk. This way it separates from projects that have skipped for assigning risks vs project that truly have no risk.

Thanks again.

In D2 control+shift+enter, not just enter, and copy down:

=IF(C2,COUNTIFS(Sheet1!$A$2:$A$10,A2,Sheet1!$B$2:$B$10,"?*",Sheet1!$D$2:$D$10,"R")&" action(s) "&TEXTJOIN(", ",TRUE,IF(Sheet1!$A$2:$A$10=$A2,IF(Sheet1!$D$2:$D$10="R",Sheet1!$B$2:$B$10,""),""))&" is/are at risk","Risks not identified")