extracting month and year from column of dates

Nova1979

Board Regular
Joined
Feb 4, 2020
Messages
100
Office Version
  1. 2010
Platform
  1. Windows
Hello All,
Hopefully a simple question. I have a column of dates on 2 different sheets (E:E - same column both sheets). These are standard dd/mm/yy entries.
I am hoping to get a formula that I can place in a sheet3 A:A that will check these dates and only list Month and year (Jan-20) once so I end up with a list that will add a new month when it recognises a new date has been entered in sheet1 or sheet2.

From this located in column E:E on sheet 1 and 2
1603759328476.png


To This located on sheet3 A:A
1603759351600.png

The reason for this is sheet3 is an overview of the data relating to these dates in sheet1 and 2 as well as other formulation in sheet3 and graphs

I apologise I am only able to supply images, however I am hoping this is ok

Thanks
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

hnsd24_CN

Board Regular
Joined
Oct 13, 2020
Messages
78
Office Version
  1. 2016
Platform
  1. Windows
I admit it's a very stupid approach,the formula seems too complicated
The difficulty is that the data is distributed across two worksheets,you must use some methods to join memory array together.
If use auxiliary columns or auxiliary worksheet,the method becomes simpler.
Looking forward to better solutions,no auxiliary columns,haha
sheet3
Cell Formulas
RangeFormula
A2:A20A2=IFERROR(TEXT(INDEX(IFERROR(SMALL(IFERROR(IF({1,0},INDIRECT("Sheet1!E2:E"&COUNTA(Sheet1!E:E)),INDIRECT("Sheet2!E2:E"&COUNTA(Sheet2!E:E))),""),ROW(INDIRECT("$1:$"&MAX(COUNTA(Sheet1!E:E),COUNTA(Sheet2!E:E))*2-2))),""),SMALL(IF(MATCH(IFERROR(SMALL(IFERROR(IF({1,0},INDIRECT("Sheet1!E2:E"&COUNTA(Sheet1!E:E)),INDIRECT("Sheet2!E2:E"&COUNTA(Sheet2!E:E))),""),ROW(INDIRECT("$1:$"&MAX(COUNTA(Sheet1!E:E),COUNTA(Sheet2!E:E))*2-2))),""),IFERROR(SMALL(IFERROR(IF({1,0},INDIRECT("Sheet1!E2:E"&COUNTA(Sheet1!E:E)),INDIRECT("Sheet2!E2:E"&COUNTA(Sheet2!E:E))),""),ROW(INDIRECT("$1:$"&MAX(COUNTA(Sheet1!E:E),COUNTA(Sheet2!E:E))*2-2))),""),)=ROW(INDIRECT("$1:$"&MAX(COUNTA(Sheet1!E:E),COUNTA(Sheet2!E:E))*2-2)),ROW(INDIRECT("$1:$"&MAX(COUNTA(Sheet1!E:E),COUNTA(Sheet2!E:E))*2-2)),4^8),ROW(A1))),"[$-en-US]d-mmm;@"),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

sheet1
test2.xlsx
ABCDE
1DATE
227-Oct-20
327-Oct-20
427-Oct-20
526-Oct-20
626-Oct-20
725-Oct-20
825-Oct-20
924-Oct-20
1024-Oct-20
1124-Oct-20
1224-Oct-20
1324-Oct-20
1424-Oct-20
1523-Oct-20
1622-Oct-20
1722-Oct-20
1821-Oct-20
1921-Oct-20
2021-Oct-20
21
Sheet1

sheet2
test2.xlsx
ABCDE
1DATE
220-Oct-20
320-Oct-20
420-Oct-20
519-Oct-20
619-Oct-20
718-Oct-20
818-Oct-20
918-Oct-20
1018-Oct-20
1118-Oct-20
1218-Oct-20
1317-Oct-20
1417-Oct-20
1517-Oct-20
1617-Oct-20
1716-Oct-20
1816-Oct-20
1916-Oct-20
2015-Oct-20
2114-Oct-20
Sheet2
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,000
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
With Power Query, bring each table into the PQ editor. Append one to the other. Then delete duplicates and sort.

Power Query:
let
    Source = Table.Combine({Table1, Table2}),
    #"Removed Duplicates" = Table.Distinct(Source),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"DATE", Order.Ascending}})
in
    #"Sorted Rows"
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,471
Office Version
  1. 365
Platform
  1. Windows
How do you end up with Mar-20 and Apr-20 in the results when there are no dates in March or April 2020 in your original list of dates?
 

Nova1979

Board Regular
Joined
Feb 4, 2020
Messages
100
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Hello all and thanks for the responses so far.

hsnd24_CN - You have listed ALL dates that have occurred in sheet3. This is not quite what I am after.
alansidman - unfortunately I do not have access to Power Query
Peter_SSs - this is a sample I pulled. The current data in sheet 1 and 2 which is being refenced is over 800 rows and growing.

When I manually enter Oct-20 into respective cell in column A sheet3 (A8 using the supplied example part of Month-Year) other formulas on the respective row start populating as they are dependant on

As I am still a learner when it come to Excel, is it possible to have something similar to the following:

IF Sheet1 E:E OR Sheet2 E:E contains a date ranging between 01-Oct-2020 and 31-Oct-2020 then place Oct-20 in the cell otherwise leave blank.

The idea is that when a date in the formula range is entered into sheet1 E:E or sheet2 E:E then sheet3 will auto populate. This then will automate sheet3 without manual intervention.

This would be placed in the relative cell I want Oct-20 to appear, and I could then adjust as required for Dec-20, Jan-21 etc.
This would also make it easier if other sheets were created requiring the same data to be captured (god I hope not) by adding the relative sheet information to the OR put of the formula

I understand that it is difficult to come up with something without the actual data in front of you, however due to what it is, I am unable to do this and am grateful for any and all assistance
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,000
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Power Query is a free add in to Excel 2010.

 

Nova1979

Board Regular
Joined
Feb 4, 2020
Messages
100
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Hi alan,
Yes it is, however I am on a network that will not allow addons or anything beyond what I am supplied with. This also means I am unable to send the data to my personal email.

Its a case of doing the best I can with what I have available (and having my hands tied). Trust me, it is very frustrating.
 

jocote46

New Member
Joined
May 11, 2009
Messages
43
you need to add helper columns to add the month and year. that's how i do it. use the following formula on Sheet3 =text(sheet1.E:E Cell, "MMM") for month and then add another column on Sheet3 fore the year =text(sheet1, cell where date is, "YYYY") and then copy the formula all the way down. good luck.
 

Nova1979

Board Regular
Joined
Feb 4, 2020
Messages
100
Office Version
  1. 2010
Platform
  1. Windows
I have put this together to show the month and year result I am chasing. As can be seen, column A only has a month and year reference. The data in the remaining Columns is the number of occurrences between the start and end of the Months. These are then converted to graphs. The data in columns B onwards will populate only when something is placed in Column A.
Cell Formulas
RangeFormula
B3:M3B3=SUM(B4:B37)
B4B4=IF(A4="","",COUNTIFS(AABD!$E$3:$E$90,">=1/3/2020",AABD!$E$3:$E$90,"<=30/03/2020"))
C4C4=IF(A4="","",COUNTIFS(AABD!$H$3:$H$90,">=1/3/2020",AABD!$H$3:$H$90,"<=30/03/2020"))
D4D4=IF(A4="","",COUNTIFS(AABD!$I$3:$I$90,">=1/4/2020",AABD!$I$3:$I$90,"<=30/04/2020"))
E4E4=IF(A4="","",COUNTIFS(NBAN!$E$3:$E$35,">=1/03/2020",NBAN!$E$3:$E$35,"<=30/03/2020"))
F4F4=IF(A4="","",COUNTIFS(NBAN!$H$3:$H$35,">=1/3/2020",NBAN!$H$3:$H$35,"<=30/03/2020"))
G4G4=IF(A4="","",COUNTIFS(NBAN!$I$3:$I$35,">=1/3/2020",NBAN!$I$3:$I$35,"<=30/03/2020"))
H4H4=IF(A4="","",COUNTIFS('SCA Transfers'!$G$3:$G$150,">=1/3/2020",'SCA Transfers'!$G$3:$G$150,"<=30/03/2020"))
I4I4=IF(A4="","",COUNTIFS('SCA Transfers'!$I$3:$I$150,">=1/3/2020",'SCA Transfers'!$I$3:$I$150,"<=30/03/2020"))
J4J4=IF(A4="","",COUNTIFS('SCA Transfers'!$J$3:$J$150,">=1/3/2020",'SCA Transfers'!$J$3:$J$150,"<=30/03/2020"))
K4K4=IF(A4="","",COUNTIFS(IDT!$H$3:$H$90,">=1/3/2020",IDT!$H$3:$H$90,"<=30/03/2020"))
L4L4=IF(A4="","",COUNTIFS(IDT!$K$3:$K$90,">=1/3/2020",IDT!$K$3:$K$90,"<=30/03/2020"))
M4M4=IF(A4="","",COUNTIFS(IDT!$L$3:$L$90,">=1/3/2020",IDT!$L$3:$L$90,"<=30/03/2020"))
B5B5=IF(A5="","",COUNTIFS(AABD!$E$3:$E$90,">=1/4/2020",AABD!$E$3:$E$90,"<=30/04/2020"))
C5C5=IF(A5="","",COUNTIFS(AABD!$H$3:$H$90,">=1/4/2020",AABD!$H$3:$H$90,"<=30/04/2020"))
D5D5=IF(A5="","",COUNTIFS(AABD!$I$3:$I$90,">=1/3/2020",AABD!$I$3:$I$90,"<=30/03/2020"))
E5E5=IF(A5="","",COUNTIFS(NBAN!$E$3:$E$35,">=1/4/2020",NBAN!$E$3:$E$35,"<=30/04/2020"))
F5F5=IF(A5="","",COUNTIFS(NBAN!$H$3:$H$35,">=1/4/2020",NBAN!$H$3:$H$35,"<=30/04/2020"))
G5G5=IF(A5="","",COUNTIFS(NBAN!$I$3:$I$35,">=1/4/2020",NBAN!$I$3:$I$35,"<=30/04/2020"))
H5H5=IF(A5="","",COUNTIFS('SCA Transfers'!$G$3:$G$150,">=1/4/2020",'SCA Transfers'!$G$3:$G$150,"<=30/04/2020"))
I5I5=IF(A5="","",COUNTIFS('SCA Transfers'!$I$3:$I$150,">=1/4/2020",'SCA Transfers'!$I$3:$I$150,"<=30/04/2020"))
J5J5=IF(A5="","",COUNTIFS('SCA Transfers'!$J$3:$J$150,">=1/4/2020",'SCA Transfers'!$J$3:$J$150,"<=30/04/2020"))
K5K5=IF(A5="","",COUNTIFS(IDT!$H$3:$H$90,">=1/4/2020",IDT!$H$3:$H$90,"<=30/04/2020"))
L5L5=IF(A5="","",COUNTIFS(IDT!$K$3:$K$90,">=1/4/2020",IDT!$K$3:$K$90,"<=30/04/2020"))
M5M5=IF(A5="","",COUNTIFS(IDT!$L$3:$L$90,">=1/4/2020",IDT!$L$3:$L$90,"<=30/04/2020"))
B6B6=IF(A6="","",COUNTIFS(AABD!$E$3:$E$90,">=1/5/2020",AABD!$E$3:$E$90,"<=30/05/2020"))
C6C6=IF(A6="","",COUNTIFS(AABD!$H$3:$H$90,">=1/5/2020",AABD!$H$3:$H$90,"<=31/05/2020"))
D6D6=IF(A6="","",COUNTIFS(AABD!$I$3:$I$90,">=1/5/2020",AABD!$I$3:$I$90,"<=31/05/2020"))
E6E6=IF(A6="","",COUNTIFS(NBAN!$E$3:$E$35,">=1/5/2020",NBAN!$E$3:$E$35,"<=31/05/2020"))
F6F6=IF(A6="","",COUNTIFS(NBAN!$H$3:$H$35,">=1/5/2020",NBAN!$H$3:$H$35,"<=31/05/2020"))
G6G6=IF(A6="","",COUNTIFS(NBAN!$I$3:$I$35,">=1/5/2020",NBAN!$I$3:$I$35,"<=31/05/2020"))
H6H6=IF(A6="","",COUNTIFS('SCA Transfers'!$G$3:$G$150,">=1/5/2020",'SCA Transfers'!$G$3:$G$150,"<=31/05/2020"))
I6I6=IF(A6="","",COUNTIFS('SCA Transfers'!$I$3:$I$150,">=1/5/2020",'SCA Transfers'!$I$3:$I$150,"<=31/05/2020"))
J6J6=IF(A6="","",COUNTIFS('SCA Transfers'!$J$3:$J$150,">=1/5/2020",'SCA Transfers'!$J$3:$J$150,"<=31/05/2020"))
K6K6=IF(A6="","",COUNTIFS(IDT!$H$3:$H$90,">=1/5/2020",IDT!$H$3:$H$90,"<=30/05/2020"))
L6L6=IF(A6="","",COUNTIFS(IDT!$K$3:$K$90,">=1/5/2020",IDT!$K$3:$K$90,"<=30/05/2020"))
M6M6=IF(A6="","",COUNTIFS(IDT!$L$3:$L$90,">=1/5/2020",IDT!$L$3:$L$90,"<=30/05/2020"))
B7B7=IF(A7="","",COUNTIFS(AABD!$E$3:$E$90,">=1/6/2020",AABD!$E$3:$E$90,"<=30/06/2020"))
C7C7=IF(A7="","",COUNTIFS(AABD!$H$3:$H$90,">=1/6/2020",AABD!$H$3:$H$90,"<=30/06/2020"))
D7D7=IF(A7="","",COUNTIFS(AABD!$I$3:$I$90,">=1/6/2020",AABD!$I$3:$I$90,"<=30/06/2020"))
E7E7=IF(A7="","",COUNTIFS(NBAN!$E$3:$E$35,">=1/6/2020",NBAN!$E$3:$E$35,"<=31/06/2020"))
F7F7=IF(A7="","",COUNTIFS(NBAN!$H$3:$H$35,">=1/6/2020",NBAN!$H$3:$H$35,"<=31/06/2020"))
G7G7=IF(A7="","",COUNTIFS(NBAN!$I$3:$I$35,">=1/6/2020",NBAN!$I$3:$I$35,"<=31/06/2020"))
H7H7=IF(A7="","",COUNTIFS('SCA Transfers'!$G$3:$G$150,">=1/6/2020",'SCA Transfers'!$G$3:$G$150,"<=30/06/2020"))
I7I7=IF(A7="","",COUNTIFS('SCA Transfers'!$I$3:$I$150,">=1/6/2020",'SCA Transfers'!$I$3:$I$150,"<=30/06/2020"))
J7J7=IF(A7="","",COUNTIFS('SCA Transfers'!$J$3:$J$150,">=1/6/2020",'SCA Transfers'!$J$3:$J$150,"<=30/06/2020"))
K7K7=IF(A7="","",COUNTIFS(IDT!$H$3:$H$90,">=1/6/2020",IDT!$H$3:$H$90,"<=30/06/2020"))
L7L7=IF(A7="","",COUNTIFS(IDT!$K$3:$K$90,">=1/6/2020",IDT!$K$3:$K$90,"<=30/06/2020"))
M7M7=IF(A7="","",COUNTIFS(IDT!$L$3:$L$90,">=1/6/2020",IDT!$L$3:$L$90,"<=30/06/2020"))
B8B8=IF(A8="","",COUNTIFS(AABD!$E$3:$E$150,">=1/7/2020",AABD!$E$3:$E$150,"<=31/07/2020"))
C8C8=IF(A8="","",COUNTIFS(AABD!$H$3:$H$90,">=1/7/2020",AABD!$H$3:$H$90,"<=31/07/2020"))
D8D8=IF(A8="","",COUNTIFS(AABD!$I$3:$I$90,">=1/7/2020",AABD!$I$3:$I$90,"<=31/07/2020"))
E8E8=IF(A8="","",COUNTIFS(NBAN!$E$3:$E$35,">=1/7/2020",NBAN!$E$3:$E$35,"<=31/07/2020"))
F8F8=IF(A8="","",COUNTIFS(NBAN!$H$3:$H$35,">=1/7/2020",NBAN!$H$3:$H$35,"<=31/07/2020"))
G8G8=IF(A8="","",COUNTIFS(NBAN!$I$3:$I$35,">=1/7/2020",NBAN!$I$3:$I$35,"<=31/07/2020"))
H8H8=IF(A8="","",COUNTIFS('SCA Transfers'!$G$3:$G$150,">=1/7/2020",'SCA Transfers'!$G$3:$G$150,"<=31/07/2020"))
I8I8=IF(A8="","",COUNTIFS('SCA Transfers'!$I$3:$I$150,">=1/7/2020",'SCA Transfers'!$I$3:$I$150,"<=31/07/2020"))
J8J8=IF(A8="","",COUNTIFS('SCA Transfers'!$J$3:$J$150,">=1/7/2020",'SCA Transfers'!$J$3:$J$150,"<=31/07/2020"))
K8K8=IF(A8="","",COUNTIFS(IDT!$H$3:$H$90,">=1/7/2020",IDT!$H$3:$H$90,"<=31/07/2020"))
L8L8=IF(A8="","",COUNTIFS(IDT!$K$3:$K$90,">=1/7/2020",IDT!$K$3:$K$90,"<=31/07/2020"))
M8M8=IF(A8="","",COUNTIFS(IDT!$L$3:$L$90,">=1/7/2020",IDT!$L$3:$L$90,"<=31/07/2020"))
B9B9=IF(A9="","",COUNTIFS(AABD!$E$3:$E$1500,">=1/8/2020",AABD!$E$3:$E$1500,"<=31/08/2020"))
C9C9=IF(A9="","",COUNTIFS(AABD!$H$3:$H$1500,">=1/8/2020",AABD!$H$3:$H$1500,"<=31/08/2020"))
D9D9=IF(A9="","",COUNTIFS(AABD!$I$3:$I$1500,">=1/8/2020",AABD!$I$3:$I$1500,"<=31/08/2020"))
E9E9=IF(A9="","",COUNTIFS(NBAN!$E$3:$E$155,">=1/8/2020",NBAN!$E$3:$E$155,"<=31/08/2020"))
F9F9=IF(A9="","",COUNTIFS(NBAN!$H$3:$H$155,">=1/8/2020",NBAN!$H$3:$H$155,"<=31/08/2020"))
G9G9=IF(A9="","",COUNTIFS(NBAN!$I$3:$I$155,">=1/8/2020",NBAN!$I$3:$I$155,"<=31/08/2020"))
H9H9=IF(A9="","",COUNTIFS('SCA Transfers'!$G$3:$G$150,">=1/8/2020",'SCA Transfers'!$G$3:$G$150,"<=31/08/2020"))
I9I9=IF(A9="","",COUNTIFS('SCA Transfers'!$I$3:$I$150,">=1/8/2020",'SCA Transfers'!$I$3:$I$150,"<=31/08/2020"))
J9J9=IF(A9="","",COUNTIFS('SCA Transfers'!$J$3:$J$150,">=1/8/2020",'SCA Transfers'!$J$3:$J$150,"<=31/08/2020"))
K9K9=IF(A9="","",COUNTIFS(IDT!$H$3:$H$90,">=1/8/2020",IDT!$H$3:$H$90,"<=31/08/2020"))
L9L9=IF(A9="","",COUNTIFS(IDT!$K$3:$K$90,">=1/8/2020",IDT!$K$3:$K$90,"<=31/08/2020"))
M9M9=IF(A9="","",COUNTIFS(IDT!$L$3:$L$90,">=1/8/2020",IDT!$L$3:$L$90,"<=31/08/2020"))
B10B10=IF(A10="","",COUNTIFS(AABD!$E$3:$E$1500,">=1/9/2020",AABD!$E$3:$E$1500,"<=30/09/2020"))
C10C10=IF(A10="","",COUNTIFS(AABD!$H$3:$H$1500,">=1/9/2020",AABD!$H$3:$H$1500,"<=30/09/2020"))
D10D10=IF(A10="","",COUNTIFS(AABD!$I$3:$I$1500,">=1/9/2020",AABD!$I$3:$I$1500,"<=30/09/2020"))
E10E10=IF(A10="","",COUNTIFS(NBAN!$E$3:$E$155,">=1/9/2020",NBAN!$E$3:$E$155,"<=30/09/2020"))
F10F10=IF(A10="","",COUNTIFS(NBAN!$H$3:$H$155,">=1/9/2020",NBAN!$H$3:$H$155,"<=30/09/2020"))
G10G10=IF(A10="","",COUNTIFS(NBAN!$I$3:$I$155,">=1/9/2020",NBAN!$I$3:$I$155,"<=30/09/2020"))
H10H10=IF(A10="","",COUNTIFS('SCA Transfers'!$G$3:$G$150,">=1/9/2020",'SCA Transfers'!$G$3:$G$150,"<=30/09/2020"))
I10I10=IF(A10="","",COUNTIFS('SCA Transfers'!$I$3:$I$150,">=1/9/2020",'SCA Transfers'!$I$3:$I$150,"<=30/09/2020"))
J10J10=IF(A10="","",COUNTIFS('SCA Transfers'!$J$3:$J$150,">=1/9/2020",'SCA Transfers'!$J$3:$J$150,"<=30/09/2020"))
K10K10=IF(A10="","",COUNTIFS(IDT!$H$3:$H$90,">=1/9/2020",IDT!$H$3:$H$90,"<=31/09/2020"))
L10L10=IF(A10="","",COUNTIFS(IDT!$K$3:$K$90,">=1/9/2020",IDT!$K$3:$K$90,"<=31/09/2020"))
M10M10=IF(A10="","",COUNTIFS(IDT!$L$3:$L$90,">=1/9/2020",IDT!$L$3:$L$90,"<=31/09/2020"))
B11B11=IF(A11="","",COUNTIFS(AABD!$E$3:$E$1500,">=1/10/2020",AABD!$E$3:$E$1500,"<=31/10/2020"))
C11C11=IF(A11="","",COUNTIFS(AABD!$H$3:$H$1500,">=1/10/2020",AABD!$H$3:$H$1500,"<=31/10/2020"))
D11D11=IF(A11="","",COUNTIFS(AABD!$I$3:$I$1500,">=1/10/2020",AABD!$I$3:$I$1500,"<=31/10/2020"))
E11E11=IF(A11="","",COUNTIFS(NBAN!$E$3:$E$1500,">=1/10/2020",NBAN!$E$3:$E$1500,"<=31/10/2020"))
F11F11=IF(A11="","",COUNTIFS(NBAN!$H$3:$H$1500,">=1/10/2020",NBAN!$H$3:$H$1500,"<=31/10/2020"))
G11G11=IF(A11="","",COUNTIFS(NBAN!$I$3:$I$1500,">=1/10/2020",NBAN!$I$3:$I$1500,"<=31/10/2020"))
H11H11=IF(A11="","",COUNTIFS('SCA Transfers'!$G$3:$G$1500,">=1/10/2020",'SCA Transfers'!$G$3:$G$1500,"<=31/10/2020"))
I11I11=IF(A11="","",COUNTIFS('SCA Transfers'!$I$3:$I$1500,">=1/10/2020",'SCA Transfers'!$I$3:$I$1500,"<=31/10/2020"))
J11J11=IF(A11="","",COUNTIFS('SCA Transfers'!$J$3:$J$1500,">=1/10/2020",'SCA Transfers'!$J$3:$J$1500,"<=31/10/2020"))
K11K11=IF(A11="","",COUNTIFS(IDT!$H$3:$H$1500,">=1/10/2020",IDT!$H$3:$H$1500,"<=31/10/2020"))
L11L11=IF(A11="","",COUNTIFS(IDT!$K$3:$K$1500,">=1/10/2020",IDT!$K$3:$K$1500,"<=31/10/2020"))
M11M11=IF(A11="","",COUNTIFS(IDT!$L$3:$L$1500,">=1/10/2020",IDT!$L$3:$L$1500,"<=31/10/2020"))
B12B12=IF(A12="","",COUNTIFS(AABD!$E$3:$E$1500,">=1/11/2020",AABD!$E$3:$E$1500,"<=31/11/2020"))
C12C12=IF(A12="","",COUNTIFS(AABD!$H$3:$H$1500,">=1/11/2020",AABD!$H$3:$H$1500,"<=31/11/2020"))
D12D12=IF(A12="","",COUNTIFS(AABD!$I$3:$I$1500,">=1/11/2020",AABD!$I$3:$I$1500,"<=31/11/2020"))
E12E12=IF(A12="","",COUNTIFS(NBAN!$E$3:$E$1500,">=1/11/2020",NBAN!$E$3:$E$1500,"<=31/11/2020"))
F12F12=IF(A12="","",COUNTIFS(NBAN!$H$3:$H$1500,">=1/11/2020",NBAN!$H$3:$H$1500,"<=31/11/2020"))
G12G12=IF(A12="","",COUNTIFS(NBAN!$I$3:$I$1500,">=1/11/2020",NBAN!$I$3:$I$1500,"<=31/11/2020"))
H12H12=IF(A12="","",COUNTIFS('SCA Transfers'!$G$3:$G$1500,">=1/11/2020",'SCA Transfers'!$G$3:$G$1500,"<=31/11/2020"))
I12I12=IF(A12="","",COUNTIFS('SCA Transfers'!$I$3:$I$1500,">=1/11/2020",'SCA Transfers'!$I$3:$I$1500,"<=31/11/2020"))
J12J12=IF(A12="","",COUNTIFS('SCA Transfers'!$J$3:$J$1500,">=1/11/2020",'SCA Transfers'!$J$3:$J$1500,"<=31/11/2020"))
K12K12=IF(A12="","",COUNTIFS(IDT!$H$3:$H$1500,">=1/11/2020",IDT!$H$3:$H$1500,"<=31/11/2020"))
L12L12=IF(A12="","",COUNTIFS(IDT!$K$3:$K$1500,">=1/11/2020",IDT!$K$3:$K$1500,"<=31/11/2020"))
M12M12=IF(A12="","",COUNTIFS(IDT!$L$3:$L$1500,">=1/11/2020",IDT!$L$3:$L$1500,"<=31/11/2020"))
B13B13=IF(A13="","",COUNTIFS(AABD!$E$3:$E$1500,">=1/12/2020",AABD!$E$3:$E$1500,"<=31/12/2020"))
C13C13=IF(A13="","",COUNTIFS(AABD!$H$3:$H$1500,">=1/12/2020",AABD!$H$3:$H$1500,"<=31/12/2020"))
D13D13=IF(A13="","",COUNTIFS(AABD!$I$3:$I$1500,">=1/12/2020",AABD!$I$3:$I$1500,"<=31/12/2020"))
E13E13=IF(A13="","",COUNTIFS(NBAN!$E$3:$E$1500,">=1/12/2020",NBAN!$E$3:$E$1500,"<=31/12/2020"))
F13F13=IF(A13="","",COUNTIFS(NBAN!$H$3:$H$1500,">=1/12/2020",NBAN!$H$3:$H$1500,"<=31/12/2020"))
G13G13=IF(A13="","",COUNTIFS(NBAN!$I$3:$I$1500,">=1/12/2020",NBAN!$I$3:$I$1500,"<=31/12/2020"))
H13H13=IF(A13="","",COUNTIFS('SCA Transfers'!$G$3:$G$1500,">=1/12/2020",'SCA Transfers'!$G$3:$G$1500,"<=31/12/2020"))
I13I13=IF(A13="","",COUNTIFS('SCA Transfers'!$I$3:$I$1500,">=1/12/2020",'SCA Transfers'!$I$3:$I$1500,"<=31/12/2020"))
J13J13=IF(A13="","",COUNTIFS('SCA Transfers'!$J$3:$J$1500,">=1/12/2020",'SCA Transfers'!$J$3:$J$1500,"<=31/12/2020"))
K13K13=IF(A13="","",COUNTIFS(IDT!$H$3:$H$1500,">=1/12/2020",IDT!$H$3:$H$1500,"<=31/12/2020"))
L13L13=IF(A13="","",COUNTIFS(IDT!$K$3:$K$1500,">=1/12/2020",IDT!$K$3:$K$1500,"<=31/12/2020"))
M13M13=IF(A13="","",COUNTIFS(IDT!$L$3:$L$1500,">=1/12/2020",IDT!$L$3:$L$1500,"<=31/12/2020"))
B14B14=IF(A14="","",COUNTIFS(AABD!$E$3:$E$1500,">=1/1/2021",AABD!$E$3:$E$1500,"<=31/1/2021"))
C14C14=IF(A14="","",COUNTIFS(AABD!$H$3:$H$1500,">=1/1/2021",AABD!$H$3:$H$1500,"<=31/1/2021"))
D14D14=IF(A14="","",COUNTIFS(AABD!$I$3:$I$1500,">=1/1/2021",AABD!$I$3:$I$1500,"<=31/1/2021"))
E14E14=IF(A14="","",COUNTIFS(NBAN!$E$3:$E$1500,">=1/1/2021",NBAN!$E$3:$E$1500,"<=31/1/2021"))
F14F14=IF(A14="","",COUNTIFS(NBAN!$H$3:$H$1500,">=1/1/2021",NBAN!$H$3:$H$1500,"<=31/1/2021"))
G14G14=IF(A14="","",COUNTIFS(NBAN!$I$3:$I$1500,">=1/1/2021",NBAN!$I$3:$I$1500,"<=31/1/2021"))
H14H14=IF(A14="","",COUNTIFS('SCA Transfers'!$G$3:$G$1500,">=1/1/2021",'SCA Transfers'!$G$3:$G$1500,"<=31/1/2021"))
I14:I15I14=IF(A14="","",COUNTIFS('SCA Transfers'!$I$3:$I$1500,">=1/1/2021",'SCA Transfers'!$I$3:$I$1500,"<=31/1/2021"))
J14:J15J14=IF(A14="","",COUNTIFS('SCA Transfers'!$J$3:$J$1500,">=1/1/2021",'SCA Transfers'!$J$3:$J$1500,"<=31/1/2021"))
K14K14=IF(A14="","",COUNTIFS(IDT!$H$3:$H$1500,">=1/1/2021",IDT!$H$3:$H$1500,"<=31/1/2021"))
L14L14=IF(A14="","",COUNTIFS(IDT!$K$3:$K$1500,">=1/1/2021",IDT!$K$3:$K$1500,"<=31/1/2021"))
M14M14=IF(A14="","",COUNTIFS(IDT!$L$3:$L$1500,">=1/1/2021",IDT!$L$3:$L$1500,"<=31/1/2021"))
B15B15=IF(A15="","",COUNTIFS(AABD!$E$3:$E$1500,">=1/2/2021",AABD!$E$3:$E$1500,"<=28/2/2021"))
C15C15=IF(A15="","",COUNTIFS(AABD!$H$3:$H$1500,">=1/2/2021",AABD!$H$3:$H$1500,"<=28/2/2021"))
D15D15=IF(A15="","",COUNTIFS(AABD!$I$3:$I$1500,">=1/2/2021",AABD!$I$3:$I$1500,"<=28/2/2021"))
E15E15=IF(A15="","",COUNTIFS(NBAN!$E$3:$E$1500,">=1/2/2021",NBAN!$E$3:$E$1500,"<=28/2/2021"))
F15F15=IF(A15="","",COUNTIFS(NBAN!$H$3:$H$1500,">=1/2/2021",NBAN!$H$3:$H$1500,"<=28/2/2021"))
G15G15=IF(A15="","",COUNTIFS(NBAN!$I$3:$I$1500,">=1/2/2021",NBAN!$I$3:$I$1500,"<=28/2/2021"))
H15H15=IF(A15="","",COUNTIFS('SCA Transfers'!$G$3:$G$1500,">=1/2/2021",'SCA Transfers'!$G$3:$G$1500,"<=28/2/2021"))
K15K15=IF(A15="","",COUNTIFS(IDT!$H$3:$H$1500,">=1/2/2021",IDT!$H$3:$H$1500,"<=28/2/2021"))
L15L15=IF(A15="","",COUNTIFS(IDT!$K$3:$K$1500,">=1/2/2021",IDT!$K$3:$K$1500,"<=28/2/2021"))
M15M15=IF(A15="","",COUNTIFS(IDT!$L$3:$L$1500,">=1/2/2021",IDT!$L$3:$L$1500,"<=28/2/2021"))
B16B16=IF(A16="","",COUNTIFS(AABD!$E$3:$E$1500,">=1/3/2021",AABD!$E$3:$E$1500,"<=30/03/2021"))
C16C16=IF(A16="","",COUNTIFS(AABD!$H$3:$H$1500,">=1/3/2021",AABD!$H$3:$H$1500,"<=30/03/2021"))
D16D16=IF(A16="","",COUNTIFS(AABD!$I$3:$I$1500,">=1/4/2021",AABD!$I$3:$I$1500,"<=30/04/2021"))
E16E16=IF(A16="","",COUNTIFS(NBAN!$E$3:$E$1500,">=1/03/2021",NBAN!$E$3:$E$1500,"<=30/03/2021"))
F16F16=IF(A16="","",COUNTIFS(NBAN!$H$3:$H$1500,">=1/3/2021",NBAN!$H$3:$H$1500,"<=30/03/2021"))
G16G16=IF(A16="","",COUNTIFS(NBAN!$I$3:$I$1500,">=1/3/2021",NBAN!$I$3:$I$1500,"<=30/03/2021"))
H16H16=IF(A16="","",COUNTIFS('SCA Transfers'!$G$3:$G$1500,">=1/3/2021",'SCA Transfers'!$G$3:$G$1500,"<=30/03/2021"))
I16I16=IF(A16="","",COUNTIFS('SCA Transfers'!$I$3:$I$1500,">=1/3/2021",'SCA Transfers'!$I$3:$I$1500,"<=30/03/2021"))
J16J16=IF(A16="","",COUNTIFS('SCA Transfers'!$J$3:$J$1500,">=1/3/2021",'SCA Transfers'!$J$3:$J$1500,"<=30/03/2021"))
K16K16=IF(A16="","",COUNTIFS(IDT!$H$3:$H$1500,">=1/3/2021",IDT!$H$3:$H$1500,"<=30/03/2021"))
L16L16=IF(A16="","",COUNTIFS(IDT!$K$3:$K$1500,">=1/3/2021",IDT!$K$3:$K$1500,"<=30/03/2021"))
M16M16=IF(A16="","",COUNTIFS(IDT!$L$3:$L$1500,">=1/3/2021",IDT!$L$3:$L$1500,"<=30/03/2021"))
B17B17=IF(A17="","",COUNTIFS(AABD!$E$3:$E$1500,">=1/4/2021",AABD!$E$3:$E$1500,"<=30/04/2021"))
C17C17=IF(A17="","",COUNTIFS(AABD!$H$3:$H$1500,">=1/4/2021",AABD!$H$3:$H$1500,"<=30/04/2021"))
D17D17=IF(A17="","",COUNTIFS(AABD!$I$3:$I$1500,">=1/3/2021",AABD!$I$3:$I$1500,"<=30/03/2021"))
E17E17=IF(A17="","",COUNTIFS(NBAN!$E$3:$E$1500,">=1/4/2021",NBAN!$E$3:$E$1500,"<=30/04/2021"))
F17F17=IF(A17="","",COUNTIFS(NBAN!$H$3:$H$1500,">=1/4/2021",NBAN!$H$3:$H$1500,"<=30/04/2021"))
G17G17=IF(A17="","",COUNTIFS(NBAN!$I$3:$I$1500,">=1/4/2021",NBAN!$I$3:$I$1500,"<=30/04/2021"))
H17H17=IF(A17="","",COUNTIFS('SCA Transfers'!$G$3:$G$1500,">=1/4/2021",'SCA Transfers'!$G$3:$G$1500,"<=30/04/2021"))
I17I17=IF(A17="","",COUNTIFS('SCA Transfers'!$I$3:$I$1500,">=1/4/2021",'SCA Transfers'!$I$3:$I$1500,"<=30/04/2021"))
J17J17=IF(A17="","",COUNTIFS('SCA Transfers'!$J$3:$J$1500,">=1/4/2021",'SCA Transfers'!$J$3:$J$1500,"<=30/04/2021"))
K17K17=IF(A17="","",COUNTIFS(IDT!$H$3:$H$1500,">=1/4/2021",IDT!$H$3:$H$1500,"<=30/04/2021"))
L17L17=IF(A17="","",COUNTIFS(IDT!$K$3:$K$1500,">=1/4/2021",IDT!$K$3:$K$1500,"<=30/04/2021"))
M17M17=IF(A17="","",COUNTIFS(IDT!$L$3:$L$1500,">=1/4/2021",IDT!$L$3:$L$1500,"<=30/04/2021"))
B18B18=IF(A18="","",COUNTIFS(AABD!$E$3:$E$1500,">=1/5/2021",AABD!$E$3:$E$1500,"<=30/05/2021"))
C18C18=IF(A18="","",COUNTIFS(AABD!$H$3:$H$1500,">=1/5/2021",AABD!$H$3:$H$1500,"<=31/05/2021"))
D18D18=IF(A18="","",COUNTIFS(AABD!$I$3:$I$1500,">=1/5/2021",AABD!$I$3:$I$1500,"<=31/05/2021"))
E18E18=IF(A18="","",COUNTIFS(NBAN!$E$3:$E$1500,">=1/5/2021",NBAN!$E$3:$E$1500,"<=31/05/2021"))
F18F18=IF(A18="","",COUNTIFS(NBAN!$H$3:$H$1500,">=1/5/2021",NBAN!$H$3:$H$1500,"<=31/05/2021"))
G18G18=IF(A18="","",COUNTIFS(NBAN!$I$3:$I$1500,">=1/5/2021",NBAN!$I$3:$I$1500,"<=31/05/2021"))
H18H18=IF(A18="","",COUNTIFS('SCA Transfers'!$G$3:$G$1500,">=1/5/2021",'SCA Transfers'!$G$3:$G$1500,"<=31/05/2021"))
I18I18=IF(A18="","",COUNTIFS('SCA Transfers'!$I$3:$I$1500,">=1/5/2021",'SCA Transfers'!$I$3:$I$1500,"<=31/05/2021"))
J18J18=IF(A18="","",COUNTIFS('SCA Transfers'!$J$3:$J$1500,">=1/5/2021",'SCA Transfers'!$J$3:$J$1500,"<=31/05/2021"))
K18K18=IF(A18="","",COUNTIFS(IDT!$H$3:$H$1500,">=1/5/2021",IDT!$H$3:$H$1500,"<=30/05/2021"))
L18L18=IF(A18="","",COUNTIFS(IDT!$K$3:$K$1500,">=1/5/2021",IDT!$K$3:$K$1500,"<=30/05/2021"))
M18M18=IF(A18="","",COUNTIFS(IDT!$L$3:$L$1500,">=1/5/2021",IDT!$L$3:$L$1500,"<=30/05/2021"))
B19B19=IF(A19="","",COUNTIFS(AABD!$E$3:$E$1500,">=1/6/2021",AABD!$E$3:$E$1500,"<=30/06/2021"))
C19C19=IF(A19="","",COUNTIFS(AABD!$H$3:$H$1500,">=1/6/2021",AABD!$H$3:$H$1500,"<=30/06/2021"))
D19D19=IF(A19="","",COUNTIFS(AABD!$I$3:$I$1500,">=1/6/2021",AABD!$I$3:$I$1500,"<=30/06/2021"))
E19E19=IF(A19="","",COUNTIFS(NBAN!$E$3:$E$1500,">=1/6/2021",NBAN!$E$3:$E$1500,"<=31/06/2021"))
F19F19=IF(A19="","",COUNTIFS(NBAN!$H$3:$H$1500,">=1/6/2021",NBAN!$H$3:$H$1500,"<=31/06/2021"))
G19G19=IF(A19="","",COUNTIFS(NBAN!$I$3:$I$1500,">=1/6/2021",NBAN!$I$3:$I$1500,"<=31/06/2021"))
H19H19=IF(A19="","",COUNTIFS('SCA Transfers'!$G$3:$G$1500,">=1/6/2021",'SCA Transfers'!$G$3:$G$1500,"<=30/06/2021"))
I19I19=IF(A19="","",COUNTIFS('SCA Transfers'!$I$3:$I$1500,">=1/6/2021",'SCA Transfers'!$I$3:$I$1500,"<=30/06/2021"))
J19J19=IF(A19="","",COUNTIFS('SCA Transfers'!$J$3:$J$1500,">=1/6/2021",'SCA Transfers'!$J$3:$J$1500,"<=30/06/2021"))
K19K19=IF(A19="","",COUNTIFS(IDT!$H$3:$H$1500,">=1/6/2021",IDT!$H$3:$H$1500,"<=30/06/2021"))
L19L19=IF(A19="","",COUNTIFS(IDT!$K$3:$K$1500,">=1/6/2021",IDT!$K$3:$K$1500,"<=30/06/2021"))
M19M19=IF(A19="","",COUNTIFS(IDT!$L$3:$L$1500,">=1/6/2021",IDT!$L$3:$L$1500,"<=30/06/2021"))
B20B20=IF(A20="","",COUNTIFS(AABD!$E$3:$E$1500,">=1/7/2021",AABD!$E$3:$E$1500,"<=31/07/2021"))
C20C20=IF(A20="","",COUNTIFS(AABD!$H$3:$H$1500,">=1/7/2021",AABD!$H$3:$H$1500,"<=31/07/2021"))
D20D20=IF(A20="","",COUNTIFS(AABD!$I$3:$I$1500,">=1/7/2021",AABD!$I$3:$I$1500,"<=31/07/2021"))
E20E20=IF(A20="","",COUNTIFS(NBAN!$E$3:$E$1500,">=1/7/2021",NBAN!$E$3:$E$1500,"<=31/07/2021"))
F20F20=IF(A20="","",COUNTIFS(NBAN!$H$3:$H$1500,">=1/7/2021",NBAN!$H$3:$H$1500,"<=31/07/2021"))
G20G20=IF(A20="","",COUNTIFS(NBAN!$I$3:$I$1500,">=1/7/2021",NBAN!$I$3:$I$1500,"<=31/07/2021"))
H20H20=IF(A20="","",COUNTIFS('SCA Transfers'!$G$3:$G$1500,">=1/7/2021",'SCA Transfers'!$G$3:$G$1500,"<=31/07/2021"))
I20I20=IF(A20="","",COUNTIFS('SCA Transfers'!$I$3:$I$1500,">=1/7/2021",'SCA Transfers'!$I$3:$I$1500,"<=31/07/2021"))
J20J20=IF(A20="","",COUNTIFS('SCA Transfers'!$J$3:$J$1500,">=1/7/2021",'SCA Transfers'!$J$3:$J$1500,"<=31/07/2021"))
K20K20=IF(A20="","",COUNTIFS(IDT!$H$3:$H$1500,">=1/7/2021",IDT!$H$3:$H$1500,"<=31/07/2021"))
L20L20=IF(A20="","",COUNTIFS(IDT!$K$3:$K$1500,">=1/7/2021",IDT!$K$3:$K$1500,"<=31/07/2021"))
M20M20=IF(A20="","",COUNTIFS(IDT!$L$3:$L$1500,">=1/7/2021",IDT!$L$3:$L$1500,"<=31/07/2021"))

The data is being pulled from 4 other sheets. However I am starting with Sheet1 and Sheet2 to try and establish a formula, which (hopefully) I can modify the cover the other 2. The example in the original post showing the full date in Sheet1 E:E and Shet2 E:E is where the primary data is starting.
I am hoping this clears this somewhat.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,075
Messages
5,570,054
Members
412,308
Latest member
essobsan
Top