05-06-2020 08:24 AM
Hi, albeit your question is not related to the topic in this thread, I will try to give you an answer.
there is an easy way to check if a range is sorted sorted. It is quite easy, even if you have a single blank in your data. Examples https://exceloffthegrid.com/excel-formula-to-check-if-a-list-is-sorted/
However, with the data you presented, it is much more complicated as the data is scattered with multiple blanks in between them and sometimes there is only a single value or duplicate values which makes it difficult to find a pattern for the formula.
Perhaps you can work out using some helper columns to clean the data before running the logical test.
05-06-2020 08:30 AM
The UDF did what it supposed to do. It did not work in the example you shared because when you set the third condition in red color=AND(A3>50,A3<60) this was already overlapping with the green condition, meaning that green was already counting anything above 50 . Therefore, you had a wrong count on the green when I changed the green to >=60 then it worked. see in the attached example.
05-06-2020 11:41 PM
Thank you for the quick reply! Something seems to by off with my computer/excel.
When I first open the example file you sent, I am able to see the formula working as intended. However, after enabling editing, the formula stops working and gives me the "#VALUE!" error message - as shown in the second picture. Do you have an explanation for this? Could it be that I'm using excel in a different language? (Danish)
05-07-2020 05:47 AM
Hej, It has nothing to do excel language being in Danish.
Can you try the attached version? if you still get Value error. try to press F9 and see if what happens.
if in all cases, it returns Value Error. see if you have any macros in PERSONAL.XLSB excel start up folder.
also, some add-ins does not let the UDF to work properly. so if you have any add-in activated, try to deactivate it temporary and check the file.
05-11-2020 01:41 AM
I tried opening the excel file on my personal laptop and it worked fine, however it still doesn't work on my work laptop. On my work laptop I do not have any add-inns activated and my XLSTART folder is empty. Pressing F9 just makes the VALUE! flash and nothing happens.
My personal computer uses Microsoft 365 for enterprise (excel version 2004), and my work computer uses Microsoft Office 365 ProPlus (excel version 1908) - could this have something to do with the error?
05-13-2020 05:29 PM
No, it has nothing to do with version of Excel. I tried it both with the enterprise version exactly as you have, as well as the 365 version. both worked for me. initially It 365 it shows Value error but click one of the cells with formula press F2 to enter in edit mode and enter. it should return the correct value.
please try it in the attached version.
06-10-2020 10:25 PM
I need to count by font color by conditional formatting. Pls advice how to do?
attached is the file.
Count how many cell above 7, below 3 and between 3-7
06-11-2020 12:57 AM
I have used your code on the first page, works wonderfully.
However, my cells are formatted using conditional formatting 3 times, changing colour each time. Is there a way to count the 'current colour' only? For example, the top box should be 9 but it's counting the first colour only.
06-15-2020 12:25 AM
Hi, you did not set your conditional formattings using formula. that is why it does not work.
please see an example file with the code for counting based on the font color set by conditional formatting in my answer here https://techcommunity.microsoft.com/t5/excel/sum-by-color-when-colors-are-set-by-conditional-formatt...
06-15-2020 12:27 AM
It will work as long as you do not have overlapping conditional formatted rules.
Please check if your rules are overlapping. you can upload an example file and I can look at it.
06-15-2020 12:35 AM
I've uploaded an example file.
The number in A12 and below changes colour twice. First data in F12 changes it to orange, then I12 changes it to green.
06-15-2020 12:50 AM
As I have rightly guessed. your conditional formatting rules were overlapping each other.
colors were all applied in the same column, while conditional rules were from different columns.
so, if you apply the conditional formatting columns in the columns were they are relevant then calculation will be correct. plz see example file.
also alternatively, you can also get the same result by using SUMPRODUCT function without the need for the UDF. please see example file of sumproduct.
06-15-2020 04:41 AM
Hi @Jamil Mohammad ,
Thank you for the response, however, neither spreadsheets work as planned.
1) The number of 'jobs ongoing' (red) stays the same - which it shouldn't do. When your put a date in the column for inspected, the red should go down one, and orange up one. Then again, when you put a date in date completed, green should go up 1 and orange down 1. As it stands, it looks like X amount of jobs are ongoing (not inspected) when in fact Y jobs have been inspected and should be taken off the red colour.
If this makes sense?
The job number column must also retain the colour.
06-16-2020 01:42 AM
Is there a way that the colour can just remain in the first column?