Forum Discussion

Hussein_Mohamed's avatar
Hussein_Mohamed
Brass Contributor
May 28, 2023

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 

  • HansVogelaar's avatar
    HansVogelaar
    May 28, 2023

    Hussein_Mohamed 

    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)

  • Hussein_Mohamed 

    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_Mohamed's avatar
      Hussein_Mohamed
      Brass Contributor
      Dear 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 advance
      • HansVogelaar's avatar
        HansVogelaar
        MVP

        Hussein_Mohamed 

        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)

Resources