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]

I think I might have work out why the formula you provided did not work as expected. When you put 03/31/2024 and 03/01/2024 for comparison inside COUNTIFS function, those value does not translate to equivalent the Excel sequence representing those dates but performed the calculation 3 divided by 31 divided by 2024. Instead, if you want to hardcode those dates, try <=DATEVALUE ("03/31/2024") and >=DATEVALUE ("03/01/2024") for the comparison.

 

[MuchToLearn]

Was not able to add some expended answer after the 10-minutes limitation for editing answer.

It's also possible to refer the date in another cell. Based on the sample formulas you provided, assuming cell $I$1 contains the date 03/01/2024, so <=EOMONTH ($I$1,0) and >=$I$1 for those comparison might be more appropriate. Then again, you might have good reason to hardcode those dates inside your COUNTIFS functions.

 

[MuchToLearn]

Sorry the solution I give was not correct. the correct way should be: "<="&"03/31/2024" and ">="&"03/01/2024", tricky part is the need to concatenate (using &) the comparison operators and dates as string/text type. Use of DATEVALUE is redundant then.
For using cell as reference also need to use the & like: "<="&EOMONTH ($I$1,0) and ">="&$I$1

 

[Bazabelle]

Sorry the solution I give was not correct. the correct way should be: "<="&"03/31/2024" and ">="&"03/01/2024", tricky part is the need to concatenate (using &) the comparison operators and dates as string/text type. Use of DATEVALUE is redundant then.
For using cell as reference also need to use the & like: "<="&EOMONTH ($I$1,0) and ">="&$I$1

So even if I would try this (I did not yet), my columns would still have the data reporting "14" for Colombia because there are those dates, what mostly I need is how to combine a search from one column, if the data is already there, then not to count those cells in the other columns.

 

[MuchToLearn]

Sorry for rushing in to answer this question without fully understand it. I had my own question, searching for possible solutions when I saw this question under the Unanswered Section and thought it’s simpler. Should have spent more time to understand the question correctly. Since I already moved this question out of Unanswered Section. I’ll spend more time trying to figure out.

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 Date Actual have a date (not empty) in Column AO?

Also, what exactly do you mean by ‘minus option of the previous month’? Like subtracting Column H from Column I?

In the Formula Sheet, Column G, you check Jan 2024 against RAWDATA!$AP:$AP, but for Column H & Column I, check Feb 2024 & Mar 2024 against RAWDATA!$AO:$AO instead, why?

The placing of Column AO and Column AP in the sample out of normal order, also add the confusion.

 

[MuchToLearn]

Hope I finally understood what you trying to do

So even if I would try this (I did not yet), my columns would still have the data reporting "14" for Colombia because there are those dates, what mostly I need is how to combine a search from one column, if the data is already there, then not to count those cells in the other columns.

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 Date Actual have a date (not empty) in Column AO?

Assuming 'actually occurred’ is reflected by where the Date Actual have a date (not empty) in Column AO

You were very close with your original formula just add another condition: RAWDATA!$AO:$AO,"" within all the COUNTIFS, use RAWDATA!$AP:$AP for criteria_range comparing the date (you used RAWDATA!$AO:$AO under Column I Mar 2024 for some reason), so

=IF(COUNTIFS(RAWDATA!$A:$A,$A5,RAWDATA!$AP:$AP,"<=03/31/2024",RAWDATA!$AP:$AP,">=03/01/2024",RAWDATA!$AO:$AO,"")=0,"",COUNTIFS(RAWDATA!$A:$A,$A5,RAWDATA!$AP:$AP,"<=03/31/2024",RAWDATA!$AP:$AP,">=03/01/2024",RAWDATA!$AO:$AO,""))

got me 2 when copied your sample and actually test it this time. (Note: I used the dd/mm/yyyy format 31/03/2024, 01/03/2024 when tested it, but the quoted formula above follows date convention in your formula sample)

Also, I noticed you're using version 365, so I think it's better to construct the Formula using LET, making it easier to modify and fix any issue. I don't have the access to the 365 version right now, so can't test that approach.

 

[Bazabelle]

In the Formula Sheet, Column G, you check Jan 2024 against RAWDATA!$AP:$AP, but for Column H & Column I, check Feb 2024 & Mar 2024 against RAWDATA!$AO:$AO instead, why?

So January (Summary - column G) I count only the actuals which are in RAWDATA-Column AP, the other months are planned numbers I need to see for projections, but I do not want to count the ones done in RAWDATA-Column AP that are also planned in RAWDATA-Column AO

Excel Formula:
=IF(COUNTIFS(RAWDATA!$A:$A,$A5,RAWDATA!$AP:$AP,"<=03/31/2024",RAWDATA!$AP:$AP,">=03/01/2024",RAWDATA!$AO:$AO,"")=0,"",COUNTIFS(RAWDATA!$A:$A,$A5,RAWDATA!$AP:$AP,"<=03/31/2024",RAWDATA!$AP:$AP,">=03/01/2024",RAWDATA!$AO:$AO,""))

I added it on my side but it is not showing me anything, it returns empty. I also tried changing the format date in case but same situation it remains empty.

I am not that of an expert to convert with the LET formula

 

[MuchToLearn]

The Excel workbook you have built is probably more complex than what I can infer from the sample you provided. Without knowing the exact way you set up your data, I assumed the column RAWDATA!AO the one use to determine if the data already there or not. To be more specific I thought cell RAWDATA!$AO$2, RAWDATA!$AO$3, RAWDATA!$AO$16 in your sample are blanks without data. That was the additional criteria_range my formula was based on. Correct me if I am wrong in this part of assumption.
My understanding for the indented result based on your formula logics is count the ones matching particular value of UID AND matching the Month in Date Plan AND where Date Actual do not have any value (like dates in your sample) entered.

So January (Summary - column G) I count only the actuals which are in RAWDATA-Column AP, the other months are planned numbers I need to see for projections, but I do not want to count the ones done in RAWDATA-Column AP that are also planned in RAWDATA-Column AO

In your sample data, the column RAWDATA!AP you had the heading: Date Plan, but it’s for the ones done; RAWDATA!AO you had the heading: Date Actual, but actually for planned in? Note also you ordered column RAWDATA!AO on the RHS of column RAWDATA!AP in your sample.

I added it on my side but it is not showing me anything, it returns empty. I also tried changing the format date in case but same situation it remains empty.

It would return empty if the COUNIF function returns 0, that was the logic you have built into your sample formula. You can easily verify by running the COUNIF function by itself independently. On your side maybe you have something in cell RAWDATA!$AO$2, RAWDATA!$AO$3, RAWDATA!$AO16? Since your original formula returned 14, I think the other criteria_ranges and criterias in your formula are working as intended.

Unsure if I can spend more time until the weekend, hope others could provide some help. Is there anyone that can help you in person? Since they're able to have closer look at how the actually Excel workbook is set up.

 

[Bazabelle]

The Excel workbook you have built is probably more complex than what I can infer from the sample you provided. Without knowing the exact way you set up your data, I assumed the column RAWDATA!AO the one use to determine if the data already there or not. To be more specific I thought cell RAWDATA!$AO$2, RAWDATA!$AO$3, RAWDATA!$AO$16 in your sample are blanks without data. That was the additional criteria_range my formula was based on. Correct me if I am wrong in this part of assumption.
My understanding for the indented result based on your formula logics is count the ones matching particular value of UID AND matching the Month in Date Plan AND where Date Actual do not have any value (like dates in your sample) entered.

In your sample data, the column RAWDATA!AP you had the heading: Date Plan, but it’s for the ones done; RAWDATA!AO you had the heading: Date Actual, but actually for planned in? Note also you ordered column RAWDATA!AO on the RHS of column RAWDATA!AP in your sample.

It would return empty if the COUNIF function returns 0, that was the logic you have built into your sample formula. You can easily verify by running the COUNIF function by itself independently. On your side maybe you have something in cell RAWDATA!$AO$2, RAWDATA!$AO$3, RAWDATA!$AO16? Since your original formula returned 14, I think the other criteria_ranges and criterias in your formula are working as intended.

Unsure if I can spend more time until the weekend, hope others could provide some help. Is there anyone that can help you in person? Since they're able to have closer look at how the actually Excel workbook is set up.

Thanks, I mixed the 2 columns trying to copy/paste here. I will try later to convert this with the XL2BB. I do not have a in person that can walk me through, it is not urgent fix so I can still wait when you have more time and when I can add the file in the format that will make it easier for you to understand. I truly appreciate all the time you have currently gone for this.