countifs and remove data completed

B

Bazabelle

New Member
Joined
Oct 6, 2022
Messages
34
Office Version
  1. 365
Platform
  1. Windows
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 AColumn BColumn CColumn DColumn EColumn FColumn GColumn HColumn I
UIDPerson NameAssignedCountriesNb Initial PlanNb Current Work01-Jan-2401-Feb-2401-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/56781234/5678Canada111<- 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/56781234/5678Colombia1421214<- This should return 2

TABLE WITH DATA (called RAWDATA):

Column AColumn BColumn CColumn DColumn APColumn AO
UIDDate PlanDate Actual
Colombia1234/5678Colombia1234/567819-Mar-2024
Colombia1234/5678Colombia1234/567820-Mar-2024
Colombia1234/5678Colombia1234/567805-Mar-202430-Jan-2024
Colombia1234/5678Colombia1234/567808-Mar-202431-Jan-2024
Colombia1234/5678Colombia1234/567808-Mar-202431-Jan-2024
Colombia1234/5678Colombia1234/567811-Mar-202431-Jan-2024
Colombia1234/5678Colombia1234/567811-Mar-202431-Jan-2024
Colombia1234/5678Colombia1234/567812-Mar-202431-Jan-2024
Colombia1234/5678Colombia1234/567812-Mar-202431-Jan-2024
Colombia1234/5678Colombia1234/567813-Mar-202431-Jan-2024
Colombia1234/5678Colombia1234/567813-Mar-202431-Jan-2024
Colombia1234/5678Colombia1234/567814-Mar-202431-Jan-2024
Colombia1234/5678Colombia1234/567814-Mar-202431-Jan-2024
Colombia1234/5678Colombia1234/567815-Mar-202431-Jan-2024
Canada1234/5678Canada1234/567813-Mar-2024
Canada1234/5678Canada1234/567821-Mar-202221-Mar-2022
Canada1234/5678Canada1234/567823-Jan-202320-Jan-2023
Canada1234/5678Canada1234/567802-Sep-202201-Sep-2022
Canada1234/5678Canada1234/567807-Sep-202205-Sep-2022
Canada1234/5678Canada1234/567817-Feb-202309-Jan-2023
Canada1234/5678Canada1234/567812-Sep-202212-Sep-2022
Canada1234/5678Canada1234/567816-Sep-202215-Sep-2022
Canada1234/5678Canada1234/567820-Sep-202220-Sep-2022
Canada1234/5678Canada1234/567822-May-202319-May-2023
 
Sort by date Sort by votes
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?
 
Upvote 0

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
316
dshtesy
D
S
  • Solved
Count the number of days between two dates that are in 2024
2
Replies
15
Views
1K
Fluff
Fluff
A
  • Locked
  • Question Question
Multiply an array based on date value entered.
Replies
1
Views
40
Fluff
Fluff
A
  • Question Question
Multiply an array based on date value entered.
Replies
8
Views
118
Fluff
Fluff
S
  • Question Question
Horizontal Return
Replies
6
Views
224
Fluff
Fluff

Forum statistics

Threads
1,223,889
Messages
6,175,224
Members
452,620
Latest member
dsubash

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top