SUM by colour is not giving correct results

[Anbuselvam K]

Hi

In the attached sheet sum by colour is not working or not giving proper results by using the below VBA Code

Example in cells BS6 and BR33 sum value should be 100 but there is 99

Likewise many cells different values are there.

Can anyone check and correct the code or guide me on what's the problem if the code is correct?

Thanks in advance.

Function SumByColor(CellColor As Range, rRange As Range)
Dim cSum As Long
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
If cl.Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(cl, cSum)
End If
Next cl
SumByColor = cSum
End Function

 

[Marc L]

Hi !​

​

Your bad as you use a wrong variable type without decimal !​

And formula does not require to be an array one …​

​

 

[Anbuselvam K]

Thanks, Marc L

I just changed long to double and now it is working.

 

[Marc L]

You may also affect Double type to your function.​

​

The other way is to search by format like I showed you in one of your previous theads …​

​

 

[Anbuselvam K]

Can you please suggest what should change to get the correct results?

 

[Marc L]

Was just a suggestion as your function works at is as Variant type or do you still have any issue ?​

​

 

[Anbuselvam K]

Thanks for your message

It is working in that sheet perfectly, But when I transfer to my original datasheet then it is not showing proper results as there is many different colours and structure of data.

Can you please suggest some other code or excel formula to get the results.

 

[Anbuselvam K]

The other way is to search by format like I showed you in one of your previous thead …

Dear Marc L

I am using Mac Excel where I don't have search and replace by format.

 

[Marc L]

VBA on Excel Mac can be a mess so you must specify you are on Mac when creating a thread​

as many VBA solutions under Windows can't work under Mac …​

​

But when I transfer to my original datasheet then it is not showing proper results as there is many different colours and structure of data.

The better is an attachment relative to your issue with an accurate explanation where it fails, what is the expected result …​

​

For a column you can use a filter and an easy worksheet formula even under VBA.​

​

An issue may come from human eyes : some cells appear to be the same color but in fact not when checking the cell property …​

​

 

[Marc L]

Just in case try this revamped version :​

Function SumByColor@(RColor As Range, Rg As Range)
         Dim Rc As Range
    For Each Rc In Rg
          If Rc.Interior.Color = RColor.Interior.Color Then SumByColor = SumByColor + Rc.Value2
    Next
End Function

You may Like it !​

​

 

[Anbuselvam K]

Okay I will check the cells property first.

 

[Marc L]

Try also post #10 well working on my side under Windows with your attachment …​

​

 

[Anbuselvam K]

Function SumByColor@(RColor As Range, Rg As Range)
         Dim Rc As Range
    For Each Rc In Rg
          If Rc.Interior.Color = RColor.Interior.Color Then SumByColor = SumByColor + Rc.Value2
    Next
End Function

Working Great

You may Like it !

Liked it

 

[Marc L]

Thanks.​

​

According to your post #7 no more issue ?​

​

 

[Anbuselvam K]

Yes, The new code is working on all the sheets. Thanks a lot for your support

 

[Marc L]

Ok, so it was not a Mac concern but the common trap about the 'so so' accuracy under Excel / VBA​

revealing how important is the choice of data types of variables.​

​

As I yet demonstrated seven years ago how Excel / VBA evaluates an easy operation like 20.4 - 19.6 in this thread :​

10.21-10=0.1999999999999999999

Hi Chandoo, Thank you for your blog and I am regular visiting to learn cool stuff. Today we came across a fun calculation or may look like funny to us. We were taking len(10.2-10) and it is showing 17, after checking the formula excel's answer is 10.20-10=0.199999999999999 Could you or...

chandoo.org

According to this demonstration, for accountancy / financial numbers up to 4 decimals the accurate data type is Currency​

the reason why my post #10 revamped version well works with your real workbook …​

​

 

[Anbuselvam K]

Ok, so it was not a Mac concern but the common trap about the 'so so' accuracy under Excel / VBArevealing how important is the choice of data types of variables.

Yes, Thanks.

According to this demonstration, for accountancy / financial numbers up to 4 decimals the accurate data type is Currency
the reason why my post #10 revamped version well works with your real workbook …

Well Noted.