Hi All,
I am looking for a function by which I can sum the numbers in a column in a way that three cells with green background is in between the numbers. I attached the file where I added two examles of the desired result to column D.
Thanks in advance.
Regards,
Not possible. For suming of value you may need to create criteria. Excel not read color.
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
I have now created to assist columns that work with numbers on top of the colour.
I think it is doable.
I appreciate if you have another look.
So you want to sum all 1's, all 2's etc?
=SUMIF(B:B,1,C:C)
change ,1, to ,2, etc as needed.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thanks.
It is not working for this one. I need them D columns, the first one is seen clearly, the second one is far down at row 221. Please have a look. Basically I want to sum until 3 treen is passed, and till the fourth green.
OK so what is the logic? Keep in mind that formulas cannot "see" colors.
Yes the function cannot detect the colour that's why I added the assist column. Basically the numbers in assisst column change when the sign change from minus to plus or opposite in A column. So if we can say that sum values of Col D untill the sign of column A changes thress time, that would solve the problem.
Perhaps this will be of some help.
1. Copy the formula in cell B3 up to B2 so that the numbering starts and ends on the blank cells.
2. Number column F: 4, 8, 12 etc. (series > column > step value: 4 > stop value: 72)
3. Populate column G using: =SUMIFS(C$3:C$11414,B$3:B$11414,"<="&F3,B$3:B$11414,">="&F3-3)
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Please try
1. Defind name COLORED refer =GET.CELL(63,INDIRECT("rc",FALSE))
2. In cell B3 use this formula (copy until end of data)
3. In cell D3 use this formula
Please Login or Register to view this content.
4. Save file as macro enable (.xlsm or .xlsb)Please Login or Register to view this content.
Regards.
My English is very poor, so please be patient >_<"
My Dynamic Related Dropdown post:
https://www.excelforum.com/excel-for...ml#post5657376
https://www.excelforum.com/excel-for...ml#post5655711
https://www.excelforum.com/excel-for...ml#post5655762
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks