Alternative formula to do the same task without enter the date manualy

[Hussein_Mohamed]

Dears,

Could you please assist regarding the attached formulas, as i have to enter the date range each month manualy.

Thanks 

[#2]

@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)

[#3]

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

[#4]

@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)

[#5]

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 support

[#6]

@Hussein_Mohamed 

Try

=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)

[#7]

Dear Mr. Hans,
it gives me a wrong result doesn't match with the manual figures.

Thanks

[#8]

@Hussein_Mohamed 

In that case:

=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)

[#9]

@Hans Vogelaar 

[#10 · Accepted solution]

@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)

[#11]

Thank You Mr. Hans