Free Microsoft Excel 2013 Quick Reference

Sum by cell formatting

I once read (it may have been here but I can't find it with a Search) that you could use the colours of the cell as the determination whether to include the value in a calculation. i..e if the cell is red, include the value in the total.

Anyone seen this or know how to use it?

Thanks


Post your answer or comment

comments powered by Disqus
How can I sum by cell color in Excel 2003?

Hi all

Is there anyway I can interrogate cells by what formatting they have?

For example, can I use an IF statement that tells excel to return the value
of a cell if it has a bold format, or otherwise return nothing?

I have a huge table of data that I have imported from word, that I need to
manipulate.

Any help would be much appreciated.

Regards,

Pinda.

Hi All,

I'd like to run a macro (similarly to a Change event) when the cell format
(e.g. font color) of a cell changes.
How can I do that?

Thanks,
Stefi

Hello chaps and chapesses,

I'm using this UDF to count cells based on fill color http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm

This seems a well respected method to do so, however, i can only get it to count cells of which i have manually changed the fill color.

Some of my cell colours are changed by Conditional Formatting. I cannot get this UDF to count these cells?

Can anyone help me please?

G

Hi

I am using the following formula: http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm

However I need it to add all cells by ABSOLUTE value i.e.

=SUM(ABS(M32)+ABS(P32)+ABS(AK32)+ABS(AL32)+ABS(AM32)+ABS(AP32)+ABS(AS32)+ABS(AU32)+ABS(BB32)+ABS(BE32)+ABS(BR32)+ABS(CC32)+ABS(CD32)+ABS(CI32)+ABS(DE32)+ABS(DJ32)+ABS(DL32))

Would be the sum if i manually entered the name of each coloured cell which is the same as M32?

Is there a way to edit the VB code to make it add up the cells by ABSOLUTE VALUE?

-Craig

This post is similar to a recent post by Brent W, although I'm not sure it's exactly the same.

The example is a bit contrived, but it illustrates what I am trying to do: make a summation cell show only the sum of cells in a column for rows returned by an AutoFilter selection, independent of what AutoFilter specification is selected.

Row 1 Contains summation cells
Row 2 Contains column headers
Rows 3 to N contain data

Col A: Name of Metro Area
Col B: Population of Metro Area
Col C: Contains an "x" if Metro Area has a median income over $35,000
Col C: Contains a "y" if Metro Area Has a median income over $50,000
Col D: Contains an "x" if the Metro area is northern
Col D: Contains an "y" if the Metro Area is southern

Cell B1 = sum(B3.B65536)

Col A Col B Col C Col D
Row 3: Detroit 4,000,000 (null) x
Row 4: Atlanta 3,000,000 x y
Row 5: Dayton 300,000 y x
Row 6: Macon 100,000 x y

With AutoFilter on and:

Nothing filtered, I want cell B1 to show 7,400,000

Col C filtered for I want cell B1 to show
x 3,100,000
y 300,000
(Blanks) 3,000,000
(NonBlanks) 3,400,000

Col D filtered for I want cell B1 to show
x 4,300,000
y 3,000,000
(Blanks) 0
(NonBlanks) 7,400,000

Can this be done?
If so, can it be done within Excel, or does it require VBA?
Either way, how might it be done?

TIA,

Far Farley

I received a worksheet that has strikethrough formatting on certain cells.

I want to sort all rows by the strikethrough formatting so these rows will be at the top of the worksheet.

Any help on this would be most greatly appreciated.

Count Sum By Color. I have an enormous sheet of 6000+ records. I have a macro running that permits automatic counting of cells according to their background colour. What I would like to be able to do when selecting some records is for the colour count to recognize this, and not continue to count the hidden data. Subtotal works fine regarding the displayed records, but can it be used in conjunction with the colorfunction macro so as to only count the selected record colours where applicable. I would also like to be able to sort according to the background colour, but without adding another column. Are there any boffins out there who can help???????? Please, coz it's driving me nuts!!


	VB:
	
 
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) 
    Dim rCell As Range 
    Dim lCol As Long 
    Dim vResult 
     
     
    lCol = rColor.Interior.ColorIndex 
     
    If SUM = True Then 
        For Each rCell In rRange 
            If rCell.Interior.ColorIndex = lCol Then 
                vResult = WorksheetFunction.SUM(rCell, vResult) 
            End If 
        Next rCell 
    Else 
        For Each rCell In rRange 
            If rCell.Interior.ColorIndex = lCol Then 
                vResult = 1 + vResult 
            End If 
        Next rCell 
    End If 
     
    ColorFunction = vResult 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Cell formula =colorfunction($H$10,$A:$A,FALSE) - counts colours
=subtotal(3,B12:B6000)

Hi,

I've made a quite simple work-calendar & now I have made some buttons for each type of holiday. Depending on wich button you click, the selected active cells will be coloured in the respective colour.
Now, that works & it's not that hard.

But the real problem I have is one I can't seem to fix.

If I make a User Defined Function to sum & Count like the one explained here (http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm) I get an error each time I try to insert the formula (=ColorFunction($C$1,$A$1:$A$12,FALSE) into the formula bar.

The error message tells me that the formula has an error in it and it asks me if I tried to put in a formula or not. If not I have to leave the '=' away.
Now, IT IS a formula so I don't know what I'm doing wrong...

I've uploaded the excel file to my own server because it exceeded the filesize overhere.
This is the link --> http://www.dj-jug.be/calendar-example.xls

I Hope someone can help me.

Greetzzz

I've read and used Chip Pearson's excellent VBA to count cell colors
(http://www.cpearson.com/excel/colors.aspx)

It works fine on the background color but doesn't seem to pick up any
subsequent changes to the background color made by conditional formatting.

Does anyone know of a way I can count colors of cells set by conditional
formatting

2 Excel Functions/Formulas to Count/Sum Excel Cells by Color - Excel ...

Custom Excel Formula to count and or Sum by color ... Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only $145.00 . $59.95 Instant Buy/Download ...

hi. how can i set by default the cell formatting to 'general' or 'number' or even 'text'?
i mean i dont want excel to correct let's say: 1.5 to 01.may or 9/12 to 09.dec. is this possible?
thanks.
(i have some programs which export data to excel and instead of numbers in excel appear dates and i can't set the cell format before the data is exported)

Hi there. I have created a conditional formating rule for changing the background color of cells in certain rows and now i would like to add only the values of the colored cell, and keep this formula every time the conditional formating changes colors.I know this involves some vba skills but i'm new at this.
Any help would be apreciated.
Thx in advanced.
Dragos

Hi All,

I'm at a complete loss when it comes to VBA, however I have a specific task I need to perform on a spreadsheet for work which will require the use of VBA.

We have a spreadsheet which is used to log all service calls from our customers. This spreadsheet has a multitude of columns and thousands of rows (one row per service call) one cell in these rows contains has the problem entered into which requires the service call. There is one type of problem which we are tracking as it seems to be a common occurance. This type of problem has recently started being tracked by formatting the cell background to blue.

Any of the service call rows which have a blue background in the problem cell are currently being manualy copied to a separate workbook. This isn't a particularly slow operation, however it is annoying and a duplication of work which I'd rather my staff do not have to contend with. So here's my query that hopefully you can help me with.

I need to write a piece of VBA code in the separate workbook which will check the service log for these blue background cells and then copy the relevant information from that row to the separate workbook. Ideally this code would run on opening the workbook, but I am quite happy to attach it to a button if it would make the coding easier.

Hope I have explained it clearly enough, thanks in advance for any help!

Martin

Hi all.

I'll try to explain this as best as I can. I'm trying to figure out a formula that will sum up several cells and multiply that by 10, however the minimum figure must be 10 and the maximum 100.

For example:

A B C D E F
50 1 1 1 1 1

If I have five cells with one it would calculate to 50 in cell A.

However, if the sum times 10 is say 5 I need it to be 10, and if the sum times 10 is 200 I need it to be a maximum of 100.

Something like sum of cells multiplied by 10, if less than 10 than put 10, if more than 100 than put 100.

Hope I explained it clearly enough, thanks!

Hi friends,

I would appreciate a bit of help with my below problem.

I have a table in which I have conditionally formatted certain cells.
Now I want to sum the conditionally formatted cells (that are colored distinctly) both row-wise and column-wise i.e. the sum of colored cells.
I tried applying a vba program for adding the sum of colored cells but it would not work on the conditionally formatted cells.
Could you please guide?

Thanks in advance.

Hello guys
i have problem in excel that when copying from sheet to another sheet in the same workbook it takes the format of the cell as time x.xx and applies there, regardless to the cell format of where it it goes. now if applied time-difference formula on sheet 2 it doesn't work! when entering time in sheet 2 it automatically make it as HH:MM:SS AM no matter however tried to change the cell format to time: hh:mm or to customer as h:mm@ it doesn't calculate the time different between 2 cells, one is copy-past from the previous sheet, and the new one which is inserted manually as time but never is taken as the one previously copied... therefore the output sum cell gives "VAlue" even obviously there is 2 cells with "time" but never calculating,

i think it is format of cell and we have to deal with it ? can we we force the cell format in somekind of code or making the copy-paste times from previous sheet comes without the cell format?

obviously this is to be deal with low experience IT stuff employee so should be protected not to change the cell format manually each time, they have tonly to insert the manul part then after that, the formula should caluclate this in protected cell

what you think guys? possible?

Mike

I've seen several posts for how to sum the same cells over multiple worksheets, but what I'm trying to do is sum different cells over multiple worksheets. I know what the syntax would be if I was to manually type in the formula. For instance if I wanted to sum A1 to A5 on sheet 1, B2 to B3 on sheet 2, and A20 to D20 on sheet 3, it would be

SUM(Sheet1!A1:A5, Sheet2!B2:B3, Sheet3!A20:D20)

but how can I do that by simply highlighting the desired cells and using the summation button rather than manually typing in the formula?

In excel, How can I force a cell format as text by VBA code. I have one workbook contains the value like 09/05/2005, I just want it display like this, but excel always automatically format this as date datatype.

Thanks very much for your help!

There are two or three commands that I use ALL the time that don't have button images (maybe they do in v. 2003, but I don't know).

One is the Define Name command for working with named ranges and constants. Another is Conditional Formatting.

Anyway, I created a page on my site for listing a few button images that you can download, along with how to add them to existing command buttons. If anyone has any really cool button images, I'd be more than happy to list them (and include your name if you want).

Excel Toolbar Button Images

By the way, I finally figured out how to preserve the transparency in GIF images. The trick is that you have to insert the picture into Excel first. Then copy it. Then paste the image. I listed the steps on the page so that I don't forget later.

I got a little carried away, and added a couple of other macros, such as a button (and macro) for Word Wrap and a Cell Format UserForm. Instead of creating an add-in for all this, I wrote up instructions for creating your own add-in. It's all pretty basic stuff.

If you like to add labels with a lot of subscripts, superscripts, symbol fonts, etc. like I do, then you might find the UserForm pretty fun. It was a long time ago that I created it (back when I was first learning how to create UserForms), so I'm not very sure where the original idea for such a form came from. If anyone knows, I'd like to provide a reference.

Enjoy,
Jon

Hello all,

Let me begin by apologizing if I am asking a question that has been answered already. I have searched several different ways and have been unsuccessful.

I am working on a workbook that has multiple pages of data. Not too huge. I have totals on each page, some that are dependency based. On my summary page I am using concantenation operators to fill in some totals. When I try to sum the cells (s24;s25 on SF6 Usage Report) with those operators, I get 0.

Is there a way to make the sum function compute after the cells complete their respective formulas? (if that is the problem)

I realize that this is probably the ugliest workbook you have ever seen, and I apologize. Until I agreed to take on this project, I thought I knew alot about Excel...I have since realized that I am probably not even a novice.

I have attached the workbook for you to check out. If you have any suggestion to make this book cleaner, I would greatly appreciate it.

Thank you in advance for any help you can offer.

Shawn

p.s. I am not very familiar with vba's and pivot tables, so that may be why I haven't understood that I have found an answer already.

I am trying to copy and paste Row by cell value. It is working fine when I am copying row from one worksheet by comboBox value to other worksheet. But problem is after updating data when I want copy back by cell value, it is showing "Run-time error '1004'" - Application-defined or Object-defined error.

the code below is working fine


	VB:
	
 CommandButton1_Click() 
    Dim sfind  As String 
    Dim cl     As Range 
     'check for a value & show message
     
    Sheet = "Data" 
    Sheets(Sheet).Select 
    Application.ScreenUpdating = False 
     
    If Me.userIDCombo.Value = "" Then 'the textbox in userform
         
        Me.userIDCombo.SetFocus 
         
        MsgBox "Please enter a Name to  Update." 
         
         ' show message if no Name Selected
         
        Exit Sub 
         
    Else 
         
        sfind = Me.userIDCombo.Value 
         
        With Range("A1", Range("a65536").End(xlUp)) 
             
            Set cl = .Find(sfind, LookIn:=xlValues) 
             
            If Not cl Is Nothing Then 
                 
                cl.EntireRow.Copy 
                 ' can I copy only the Range (A:G) as per comboBox
                 
                Sheets("index").Select 
                 
                Application.ScreenUpdating = False 
                 
                Range("A17").Select 
                 
                 
                ActiveSheet.Paste 
                 
                Application.CutCopyMode = False 
                 
                Unload Me 
                 'hides the userform
                 
                Sheets("index").Select 
                 
                 
            Else 
                 
                MsgBox sfind & " does not exist" 
                 
            End If 
        End With 
    End If 
    Me.userIDCombo.Value = "" '< clear box for next use
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


The code below is not working ..


	VB:
	
 saveButton_Click() 
    Dim sfind  As String 
    Dim cl     As Range 
     'check for a value & show message
     
     'Sheet = "Data"
    Sheet = "index" 
    Sheets(Sheet).Select 
    Range("A17:G17").Copy 
     
     'Application.ScreenUpdating = False
     
     
    Sheets("Data").Select 
     
    sfind = Worksheets("index").Cells(17, 1).Value 
     
    With Range("A1", Range("a65536").End(xlUp)) 
         
        Set cl = .Find(sfind, LookIn:=xlValues) 
         
        If Not cl Is Nothing Then 
             
            cl.EntireRow.Select 
             
             'Selection.Paste
             
            ActiveSheet.Paste 
             
             '      Application.CutCopyMode = False
             
             ' Unload Me
             '
             '     Sheets("index").Select
             
             
        Else 
             
            MsgBox sfind & " does not exist" 
             
        End If 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello, I'm trying to do something like this:


	VB:
	
 Range) 
     
     ' Declare Functions
    Dim rCell As Range 
    Dim rngFileName As Range 
    Dim vResult 
     
     'Load names in 'Data!A1:A10'
    Set rngFileName = Range("A1", Range("A65536").End(xlUp)) 
     
     'Loop all the filenames and sum each cell value
    For Each rCell In rngFileName 
        vResult = WorksheetFunction.Sum( '[' + rngFileName + ']Total!' + rCell) +vResult
    Next rCell 
     
    SumInd = vResult 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This function pretends to sum all the cells (With the same cell position) in almost ten files. The files are stored on a range in the same worksheet, one before another.

I think that the approach is good, but I'm not sure how to tell the sum function to get filenames, this does not work: WorksheetFunction.Sum('[' + rngFileName + ']Total!' + rCell)

Any help will be appreciated.

I have an AutoFilter list of 14,000 rows by 14 columns, and the cells have some specific formating: fill color, font,
protection status, wrap, etc....
There are an additional 7 columns of formulas to the left of the filtered range.

The strange thing is----
-WITH the formating, trying to Unfilter the list takes 2 min, via a manually activated Data>Filter>ShowAll OR via a macro run of 'ActiveSheet.ShowAllData' .
(In an attempt to optimize speed, the VBA macro sets calculation to manual before the 'ActiveSheet.ShowAllData' and screen updating set to false.)

-WITHOUT the cell formating (eg. by doing Edit>Clear>Formats), the ShowAll takes about 3 sec.

Does anyone have experience or an explanation for this?
Why should the Formating affect Filtering so much?
Options for improving speed of autofilter?

NOTE:
I don't know if, or why it would be a factor, but note that I am using Dynamic Named Range and VBA to expand/contract the formulas

and formating to size of the list/table. Although this is not done during the filtering use.

Here is the dynamic formating code


	VB:
	
 DynFmt_List() 
     
    Application.ScreenUpdating = False 
    With Application 
        .Calculation = xlManual 
    End With 
     
     '///DEFINE DYNAMIC Format List RANGE
     
     '/// dynamic range adapts to width and length of list, +501 rows
    ActiveWorkbook.Names.Add Name:="zdynFmtList", RefersTo:= _ 
    "=OFFSET(ObjectList!$I$13,0,0,501+MATCH(""*"",ObjectList!$M$13:$M$20000,-1),COLUMNS(ObjectList!$I$13:$AB$13))" 
     
     '/// Clear the formats for whole list
    Range("I15:AB20000").Select 
    Selection.ClearFormats 
     
     '/// Copy the 'seed' format from the 'top' two rows of list
    Range("I13:AB14").Select 
    Selection.Copy 
     
    Application.Goto Reference:="zdynFmtList" 
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ 
    False, Transpose:=False 
    Application.CutCopyMode = False 
     
    Application.ScreenUpdating = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I am wondering if some strange 'artifact' of manipulating the formating is becoming a factor ??

Thanks in advance for your responses.
Dave


No luck finding an answer? You could always try Google.