Summary Based on Status and Date

[kumara_faith]

Hi,

I have the following Table

TASK	ASSIGNED TO	PROGRESS	STATUS	START	END
First review - SBP/Finance/Risk/TO		100%	DONE	2-Feb-23	3-Feb-23
Second Review - - SBP/Finance/Risk/TO		100%	DONE	3-Feb-23	4-Feb-23
Review by CTOO		100%	DONE	4-Feb-23	5-Feb-23
Review by Finance		100%	DONE	5-Feb-23	6-Feb-23
Review by CFO		0%	Not Started	4-Feb-23	5-Feb-23
Functional review		96%	IN PROGRESS	9-Jul-23	12-Jul-23

In another Table, I am trying to summarize the above table as below:

Month	Task	Completed	In Progress	Not Started
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

Based on the first Table, the correct result should be as below. The Task columns shows the total task for the month regardless of status. The each column then shows whether the task has been Done, In Progress of Not Started based on the first Table above. The criteria would be to look at the Start date of all the Task for all the columns below.

Month	Task	Done	In Progress	Not Started
Feb	5	4		1
Mar
Apr
May
Jun
Jul	1		1
Aug
Sep
Oct
Nov
Dec

Appreciate any help on how to achieve this. Thank you in advance.

 

[DanteAmor]

Assuming the results in columns I to L and that in column H you have 3 letters of the month in text.

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L
1	TASK	ASSIGNED TO	PROGRESS	STATUS	START	END		Month	Task	Completed	In Progress	Not Started
2	First review		1	DONE	02/feb/2023	03/feb/2023		Feb	5	4	0	1
3	Second Review		1	DONE	03/feb/2023	04/feb/2023		Mar	0	0	0	0
4	Review by CTOO		1	DONE	04/feb/2023	05/feb/2023		Apr	0	0	0	0
5	Review by Finance		1	DONE	05/feb/2023	06/feb/2023		May	0	0	0	0
6	Review by CFO		0	Not Started	04/feb/2023	05/feb/2023		Jun	0	0	0	0
7	Functional review		0.96	IN PROGRESS	09/jul/2023	12/jul/2023		Jul	1	0	1	0
8								Aug	0	0	0	0
9								Sep	0	0	0	0
10								Oct	0	0	0	0
11								Nov	0	0	0	0
12								Dec	0	0	0	0
Hoja1

In cell I2

=SUM(IF(MONTH($E$2:$E$20)=MONTH(DATEVALUE("1"&$H2&"2023")),1))

In cell J2

=SUM(IF(MONTH($E$2:$E$20)=MONTH(DATEVALUE("1"&$H2&"2023"))*($D$2:$D$20="DONE"),1))

in cells K2 and L2

=SUM(IF(MONTH($E$2:$E$20)=MONTH(DATEVALUE("1"&$H2&"2023"))*($D$2:$D$20=K$1),1))

Copy the formulas down to the last month

 

[DanteAmor]

If in cell J1 instead of "Completed" you write "Done" then the same formula of K2 can be put in J2

 

[kumara_faith]

Hi Dante,

Thank you for the solution. That worked. Appreciate your patience and have a great day ahead.