LinkBack URL
About LinkBacks
Hello,
Requirement Manualy calculated, should update when current month is changed in Cell C1
Formula to add Values based on Criteria as month, Quarter and Year to date. Exclude those values which are Yellow highlighted
Hello,
Requirement Manualy calculated, should update when current month is changed in Cell C1
Formula to add Values based on Criteria as month, Quarter and Year to date. Exclude those values which are Yellow highlighted
You cannot reference coloured cells in a formula - formatting cannot be used. What is the reason/logic for their being highlighted?
Is this what you want for YTD?
=SUMIF(A4:A16,"<="&EOMONTH(C1,-1),B4:B16)
And MTD?
=SUMIFS(B4:B16,A4:A16,"<"&C1,A4:A16,">"&EOMONTH(C1,-1))
How are you defining your quarters?
Last edited by AliGW; 07-27-2023 at 02:54 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Reason for the highlighted is that we have to exclude these cells from addition. if colored cell exclusion is ignored then there is simple solution of Sumifs with dates as criteria.
This sample data represents amount of expense for each employee per month where highlighted represents that starting from that highlighted month employee terminated and we dont need to take expense for the purpose of reporting Month to date, Quarter to date and year to date expense.
There are hundred of employees that have highlighted cells starting from different months and i was exploring the possibility of something which can add based on date criteria and also highlighted exclusion.
I hope that answers the question and I start receiving answers to the query from honorable members of forum.
Thanks,
What is wrong with the answers I gave for YTD and MTD in post #2? Did this not help you?I hope that answers the question and I start receiving answers to the query from honorable members of forum.
I asked you to define your quesrters. I also told you that you cannot use cell highlighting as a criterion in a formula, which is why you need to incorporate the reason for the highlighting into the formula. I did this based on your expected answers.
You have completely ignored the solutions I offered and my question about quarters. Until you answer that, I can't help further.
Based on your latest post, I think your sample workbook is probably over-simplified.
Using date criteria and some additional criteria i have acheived desired results but not by utliziling highligted cells as mentioned by AliGW but by having helping Row.
Below formulas i used:
MTD =SUMIFS(B$3:B$26,$A$3:$A$26,"="&$C$30,$A$3:$A$26,"<="&B$28)
QTD = SUMIFS(B$3:B$26,$A$3:$A$26,"<="&$C$30,$A$3:$A$26,">="&DATE(YEAR($C$30),CEILING(MONTH($C$30)/3,1)*3-2,1),$A$3:$A$26,"<="&B$28)
YTD = SUMIFS(B$3:B$26,$A$3:$A$26,"<="&$C$30,$A$3:$A$26,">="&DATE(YEAR($C$30),1,1),$A$3:$A$26,"<="&B$28)
So is this now solved?
Glad to have helped.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
Thanks AliGW for your help. I really appreciate your prompt responses.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks