Forum Discussion
Alternative formula to do the same task without enter the date manualy
Dears,
Could you please assist regarding the attached formulas, as i have to enter the date range each month manualy.
Thanks
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)
- Hussein_MohamedBrass ContributorDear Mr. Hans,
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 advanceHow 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)