• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUM BASED ON VARIOUS COLOURS

S

subhapratimdas

New Member
Hi,


Would like to do categorywise sales which can be identified based on colours.


Sorry missed to attach the excel workbook

http://hotfile.com/dl/147170471/80d0946/FOOD_SALES.xlsx.html
 
Have a read of this post

http://chandoo.org/forums/topic/calculate-data-based-on-cell-color

or

http://chandoo.org/forums/topic/how-to-count-number-of-red-and-green-cells
 
Hi,


I tried pasting the vba code and adding the formula, but its still not working. I don't know much of VBA, could you please let me know, where and why its not working.


File attached

http://hotfile.com/dl/147183673/5c99ba6/FOOD_SALES.xlsm.html


Regards

Subha
 
I couldn't download your file ? Do I only guessed this is what you want to do

The instructions are in the two posts about where to put it

But basically open VBA Alt F11

Add a Code Module to your file

Find your file , right click, Insert Module

Paste the code into the right hand pane

go back to Excel Alt F11

Use the formulas as described
 
Yupp, I did that


Pasted this function


Function Cell_Math(myRange As Range, source As Range, Optional myType As Integer = 1) As Integer

'

' Use =Cell_Math( Range, Ref Range, Type)

'

' Type=0 Sum cell values that match Ref Range color

' Type=1 Counts cells that match Ref Range color


Cell_Math = 0

For Each c In myRange

If c.Interior.Color = source.Interior.Color Then

If myType = 1 Then

Cell_Math = Cell_Math + 1

Else

Cell_Math = Cell_Math + c.Value

End If

End If

Next

End Function


Then

Came back to excel sheet

=cell_math(E7:E167,D7,0)


where E7:E167 where list of values given(this cells doesn't have any colour)

D7 the colour cell for reference purpose

0 for doing sum


Output of the formula #Name?


I have reattached the link


Forum link:

http://hotfile.com/dl/147183673/5c99ba6/FOOD_SALES.xlsm.html


HTML link:

http://hotfile.com/dl/147183673/5c99ba6/FOOD_SALES.xlsm.html


http://hotfile.com/links/147183673/3cd0e39/FOOD_SALES.xlsm


Regards

Subha
 
http://hotfile.com/links/147183673/3cd0e39/FOOD_SALES.xlsm


Forum link:

http://hotfile.com/dl/147183673/5c99ba6/FOOD_SALES.xlsm.html


HTML link:

http://hotfile.com/dl/147183673/5c99ba6/FOOD_SALES.xlsm.html


I did the same

I pasted the stuff

Function Cell_Math(myRange As Range, source As Range, Optional myType As Integer = 1) As Integer

'

' Use =Cell_Math( Range, Ref Range, Type)

'

' Type=0 Sum cell values that match Ref Range color

' Type=1 Counts cells that match Ref Range color


Cell_Math = 0

For Each c In myRange

If c.Interior.Color = source.Interior.Color Then

If myType = 1 Then

Cell_Math = Cell_Math + 1

Else

Cell_Math = Cell_Math + c.Value

End If

End If

Next

End Function


Then came back to excel sheet

=cell_math(E7:E167,D7,0)

where E7:5167 is the list of values and this cell have no colours

D7 is the reference cell for choosing type of colour

0 for doing sum


Output showing #Name?


Please let me know what's the issue.


Regards

Subha
 
http://hotfile.com/links/147183673/3cd0e39/FOOD_SALES.xlsm

Forum link:

http://hotfile.com/dl/147183673/5c99ba6/FOOD_SALES.xlsm.html

HTML link:

http://hotfile.com/dl/147183673/5c99ba6/FOOD_SALES.xlsm.html


I did the same

I pasted the stuff

Function Cell_Math(myRange As Range, source As Range, Optional myType As Integer = 1) As Integer

'

' Use =Cell_Math( Range, Ref Range, Type)

'

' Type=0 Sum cell values that match Ref Range color

' Type=1 Counts cells that match Ref Range color


Cell_Math = 0

For Each c In myRange

If c.Interior.Color = source.Interior.Color Then

If myType = 1 Then

Cell_Math = Cell_Math + 1

Else

Cell_Math = Cell_Math + c.Value

End If

End If

Next

End Function


Then came back to excel sheet

=cell_math(E7:E167,D7,0)

where E7:5167 is the list of values and this cell have no colours

D7 is the reference cell for choosing type of colour

0 for doing sum


Output showing #Name?


Please let me know what's the issue.


Regards

Subha
 
I did the same

I pasted the stuff

Function Cell_Math(myRange As Range, source As Range, Optional myType As Integer = 1) As Integer

'

' Use =Cell_Math( Range, Ref Range, Type)

'

' Type=0 Sum cell values that match Ref Range color

' Type=1 Counts cells that match Ref Range color


Cell_Math = 0

For Each c In myRange

If c.Interior.Color = source.Interior.Color Then

If myType = 1 Then

Cell_Math = Cell_Math + 1

Else

Cell_Math = Cell_Math + c.Value

End If

End If

Next

End Function


Then came back to excel sheet

=cell_math(E7:E167,D7,0)

where E7:5167 is the list of values and this cell have no colours

D7 is the reference cell for choosing type of colour

0 for doing sum


Output showing #Name?


Please let me know what's the issue.


Regards

Subha


http://hotfile.com/links/147183673/3cd0e39/FOOD_SALES.xlsm


http://hotfile.com/dl/147183673/5c99ba6/FOOD_SALES.xlsm.html


http://hotfile.com/dl/147183673/5c99ba6/FOOD_SALES.xlsm.html
 
If you're getting a #NAME? error, that would seem the indicate the function is not actually in the correct place. Are you sure it's in a regular module and not a sheet or ThisWorkbook module?
 
Two issues here

1. You added the code to a worksheet module not a code module

Goto VBA, Alt F11

Right Click on any of the objects in the Food Sales Module and Insert Module

With the New Module displayed (most likely Module 1) Paste the code into the Right hand window

Go back to Excel Alt F11


2. Copy the Cell Formats from the PLU column to the Total Sales Column


3. H172 try : =cell_math(E7:E167,D172,0)


The range Total Sales has to have a Format, not the PLU column next to them


Enjoy
 
You must log in or register to reply here.
Back
Top