SOLVED

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

Brass Contributor

# 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

10 Replies

# Re: Alternative formula to do the same task without enter the date manualy

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)

# Re: Alternative formula to do the same task without enter the date manualy

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.

# Re: Alternative formula to do the same task without enter the date manualy

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

# Re: Alternative formula to do the same task without enter the date manualy

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

# Re: Alternative formula to do the same task without enter the date manualy

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)

# Re: Alternative formula to do the same task without enter the date manualy

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

Thanks

# Re: Alternative formula to do the same task without enter the date manualy

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)

# Re: Alternative formula to do the same task without enter the date manualy

best response confirmed by Hussein_Mohamed (Brass Contributor)
Solution

# Re: 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)

# Re: Alternative formula to do the same task without enter the date manualy

Thank You Mr. Hans