Forum Discussion
Alternative formula to do the same task without enter the date manualy
Sorry about that, it was "air code".
=COUNTIFS('[Summary All Company.xlsx]Summary Contracts'!$B$27:$B$2487,"Contracted",'[Summary All Company.xlsx]Summary Contracts'!$C$27:$C$2487,C5,'[Summary All Company.xlsx]Summary Contracts'!$A$27:$A$2487,"Direct",'[Summary All Company.xlsx]Summary Contracts'!$D$27:$D$2487,">="&DATE(YEAR($D$2),1,1),'[Summary All Company.xlsx]Summary Contracts'!$D$27:$D$2487,"<="&EOMONTH($D$2,0))
=SUMIFS('Summary Contracts'!$H$27:$H$2487,'Summary Contracts'!$D$27:$D$2487,">="&DATE(YEAR($D$2),1,1),'Summary Contracts'!$D$27:$D$2487,"<="&EOMONTH($D$2,0),'Summary Contracts'!$B$27:$B$2487,"Contracted",'Summary Contracts'!$A$27:$A$2487,"inDirect",'Summary Contracts'!$C$27:$C$2487,A7)
Does this do what you want?
=COUNTIFS('[Summary All Company.xlsx]Summary Contracts'!$B$27:$B$2487,"Contracted",'[Summary All Company.xlsx]Summary Contracts'!$C$27:$C$2487,C5,'[Summary All Company.xlsx]Summary Contracts'!$A$27:$A$2487,"Direct",'[Summary All Company.xlsx]Summary Contracts'!$D$27:$D$2487,">="&EOMONTH(TODAY(),-1)+1,'[Summary All Company.xlsx]Summary Contracts'!$D$27:$D$2487,"<="&EOMONTH(TODAY(),0))
=SUMIFS('Summary Contracts'!$H$27:$H$2487,'Summary Contracts'!$D$27:$D$2487,">="&EOMONTH(TODAY(),-1)+1,'Summary Contracts'!$D$27:$D$2487,"<="&EOMONTH(TODAY(),0),'Summary Contracts'!$B$27:$B$2487,"Contracted",'Summary Contracts'!$A$27:$A$2487,"inDirect",'Summary Contracts'!$C$27:$C$2487,A7)
according to the above formula it will stuck with the month before current month (month before May) i need to use the cell which i can change the month (Mar 2023 and Mar 2022 for example) and get the data accordingly.
Thanks in advance
How about this then:
=COUNTIFS('[Summary All Company.xlsx]Summary Contracts'!$B$27:$B$2487,"Contracted",'[Summary All Company.xlsx]Summary Contracts'!$C$27:$C$2487,C5,'[Summary All Company.xlsx]Summary Contracts'!$A$27:$A$2487,"Direct",'[Summary All Company.xlsx]Summary Contracts'!$D$27:$D$2487,">="&EOMONTH($D$2,-1)+1,'[Summary All Company.xlsx]Summary Contracts'!$D$27:$D$2487,"<="&EOMONTH($D$2,0))
=SUMIFS('Summary Contracts'!$H$27:$H$2487,'Summary Contracts'!$D$27:$D$2487,">="&EOMONTH($D$2,-1)+1,'Summary Contracts'!$D$27:$D$2487,"<="&EOMONTH($D$2,0),'Summary Contracts'!$B$27:$B$2487,"Contracted",'Summary Contracts'!$A$27:$A$2487,"inDirect",'Summary Contracts'!$C$27:$C$2487,A7)
- Mr. Hans,
it works well, is there any way to do the same if i want to sum based on YTD instead of month ( as a presentation for comparative Figures)
Ex) YTD Apr 2023 Vs YTD Apr 2022.
Thank you very much for your usual supportTry
=COUNTIFS('[Summary All Company.xlsx]Summary Contracts'!$B$27:$B$2487,"Contracted",'[Summary All Company.xlsx]Summary Contracts'!$C$27:$C$2487,C5,'[Summary All Company.xlsx]Summary Contracts'!$A$27:$A$2487,"Direct",'[Summary All Company.xlsx]Summary Contracts'!$D$27:$D$2487,">="&EOMONTH($D$2,-13)+1,'[Summary All Company.xlsx]Summary Contracts'!$D$27:$D$2487,"<="&EOMONTH($D$2,0))
=SUMIFS('Summary Contracts'!$H$27:$H$2487,'Summary Contracts'!$D$27:$D$2487,">="&EOMONTH($D$2,-13)+1,'Summary Contracts'!$D$27:$D$2487,"<="&EOMONTH($D$2,0),'Summary Contracts'!$B$27:$B$2487,"Contracted",'Summary Contracts'!$A$27:$A$2487,"inDirect",'Summary Contracts'!$C$27:$C$2487,A7)or perhaps
=COUNTIFS('[Summary All Company.xlsx]Summary Contracts'!$B$27:$B$2487,"Contracted",'[Summary All Company.xlsx]Summary Contracts'!$C$27:$C$2487,C5,'[Summary All Company.xlsx]Summary Contracts'!$A$27:$A$2487,"Direct",'[Summary All Company.xlsx]Summary Contracts'!$D$27:$D$2487,">="&EOMONTH($D$2,-13)+1,'[Summary All Company.xlsx]Summary Contracts'!$D$27:$D$2487,"<="&EOMONTH($D$2,-1))
=SUMIFS('Summary Contracts'!$H$27:$H$2487,'Summary Contracts'!$D$27:$D$2487,">="&EOMONTH($D$2,-13)+1,'Summary Contracts'!$D$27:$D$2487,"<="&EOMONTH($D$2,-1),'Summary Contracts'!$B$27:$B$2487,"Contracted",'Summary Contracts'!$A$27:$A$2487,"inDirect",'Summary Contracts'!$C$27:$C$2487,A7)
