countifs and remove data completed

[Bazabelle]

Hi,

I am struggling to have a formulas that would count the planning for future months but if it actually occurred, I need to remove them from the future month, if I just use the minus option of the previous month, I may get an error or inaccurate information.

I do not have the option to use XL2BB, hope the tables are pasting well.

Thanks in advance.

TABLE WITH FORMULAS:

Column A	Column B	Column C	Column D	Column E	Column F	Column G	Column H	Column I
UID	Person Name	Assigned	Countries	Nb Initial Plan	Nb Current Work	01-Jan-24	01-Feb-24	01-Mar-24
						IF(COUNTIFS(RAWDATA!$A:$A,$A3,RAWDATA!$AP:$AP,"<=01/31/2024",RAWDATA!$AP:$AP,">=01/01/2024")=0,"",COUNTIFS(RAWDATA!$A:$A,$A3,RAWDATA!$AP:$AP,"<=01/31/2024",RAWDATA!$AP:$AP,">=01/01/2024"))	IF(COUNTIFS(RAWDATA!$A:$A,$A3,RAWDATA!$AO:$AO,"<=02/29/2024",RAWDATA!$AO:$AO,">=02/01/2024")=0,"",COUNTIFS(RAWDATA!$A:$A,$A3,RAWDATA!$AO:$AO,"<=02/29/2024",RAWDATA!$AO:$AO,">=02/01/2024"))	IF(COUNTIFS(RAWDATA!$A:$A,$A3,RAWDATA!$AO:$AO,"<=03/31/2024",RAWDATA!$AO:$AO,">=03/01/2024")=0,"",COUNTIFS(RAWDATA!$A:$A,$A3,RAWDATA!$AO:$AO,"<=03/31/2024",RAWDATA!$AO:$AO,">=03/01/2024"))
Canada1234/5678		1234/5678	Canada	1	1			1	<- This needs to be 1
						IF(COUNTIFS(RAWDATA!$A:$A,$A5,RAWDATA!$AP:$AP,"<=01/31/2024",RAWDATA!$AP:$AP,">=01/01/2024")=0,"",COUNTIFS(RAWDATA!$A:$A,$A5,RAWDATA!$AP:$AP,"<=01/31/2024",RAWDATA!$AP:$AP,">=01/01/2024"))	IF(COUNTIFS(RAWDATA!$A:$A,$A5,RAWDATA!$AO:$AO,"<=02/29/2024",RAWDATA!$AO:$AO,">=02/01/2024")=0,"",COUNTIFS(RAWDATA!$A:$A,$A5,RAWDATA!$AO:$AO,"<=02/29/2024",RAWDATA!$AO:$AO,">=02/01/2024"))	IF(COUNTIFS(RAWDATA!$A:$A,$A5,RAWDATA!$AO:$AO,"<=03/31/2024",RAWDATA!$AO:$AO,">=03/01/2024")=0,"",COUNTIFS(RAWDATA!$A:$A,$A5,RAWDATA!$AO:$AO,"<=03/31/2024",RAWDATA!$AO:$AO,">=03/01/2024"))
Colombia1234/5678		1234/5678	Colombia	14	2	12		14	<- This should return 2

TABLE WITH DATA (called RAWDATA):

Column A	Column B	Column C	Column D	Column AP	Column AO
UID				Date Plan	Date Actual
Colombia1234/5678		Colombia	1234/5678	19-Mar-2024
Colombia1234/5678		Colombia	1234/5678	20-Mar-2024
Colombia1234/5678		Colombia	1234/5678	05-Mar-2024	30-Jan-2024
Colombia1234/5678		Colombia	1234/5678	08-Mar-2024	31-Jan-2024
Colombia1234/5678		Colombia	1234/5678	08-Mar-2024	31-Jan-2024
Colombia1234/5678		Colombia	1234/5678	11-Mar-2024	31-Jan-2024
Colombia1234/5678		Colombia	1234/5678	11-Mar-2024	31-Jan-2024
Colombia1234/5678		Colombia	1234/5678	12-Mar-2024	31-Jan-2024
Colombia1234/5678		Colombia	1234/5678	12-Mar-2024	31-Jan-2024
Colombia1234/5678		Colombia	1234/5678	13-Mar-2024	31-Jan-2024
Colombia1234/5678		Colombia	1234/5678	13-Mar-2024	31-Jan-2024
Colombia1234/5678		Colombia	1234/5678	14-Mar-2024	31-Jan-2024
Colombia1234/5678		Colombia	1234/5678	14-Mar-2024	31-Jan-2024
Colombia1234/5678		Colombia	1234/5678	15-Mar-2024	31-Jan-2024
Canada1234/5678		Canada	1234/5678	13-Mar-2024
Canada1234/5678		Canada	1234/5678	21-Mar-2022	21-Mar-2022
Canada1234/5678		Canada	1234/5678	23-Jan-2023	20-Jan-2023
Canada1234/5678		Canada	1234/5678	02-Sep-2022	01-Sep-2022
Canada1234/5678		Canada	1234/5678	07-Sep-2022	05-Sep-2022
Canada1234/5678		Canada	1234/5678	17-Feb-2023	09-Jan-2023
Canada1234/5678		Canada	1234/5678	12-Sep-2022	12-Sep-2022
Canada1234/5678		Canada	1234/5678	16-Sep-2022	15-Sep-2022
Canada1234/5678		Canada	1234/5678	20-Sep-2022	20-Sep-2022
Canada1234/5678		Canada	1234/5678	22-May-2023	19-May-2023

 

[MuchToLearn]

In order to help further, can you clarify a few more things I asked before?

• I don’t fully understand what you mean by ‘actually occurred’. Is this reflected some way in the data? It looks like from your sample data, it’s where there are dates (so not empty) under the heading: Date Actual in RAWDATA!AO in sample you provided in the starting post, i.e., 'data completed' is identified by having a date under the heading: Date Actual. Correct me if I am wrong in this part of assumption.
• On your side do you have something in cells: RAWDATA!$AO$2, RAWDATA!$AO$3, RAWDATA!$AO16? So there not really blank cells (as the formula provided in post #7 did give me the expected result on my side).
• Also, what exactly do you mean by ‘minus option of the previous month’? Like subtracting Column-H from Column-I in the sheet where you placed the formulas?

You know your own Excel workbook much better than me including the formulas you have built. I understand you can't share the exact data here, but if you can provide as much detail as possible... Your Excel workbook basically have those 2 sheets? and its main goal is to count the planning for each of future months?