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

## Related Results

### Sum by cell color in Excel 2003

How can I sum by cell color in Excel 2003?

### Interrogating cells by cell formatting

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.

### Event triggered by cell format change?

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

### Counting by conditionally formatted cells

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?

G

### Absolute Sum of cells by colour

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

### Sum of cells in a column of rows returned by autofilter

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
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

### Sort by cell format

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.

### Subtotal By Cell Color

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)

### Sum & Count Cells By Fill Color

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

### Counting Colors of Cells set by Conditional Formatting

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 ...

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 ...

### Format cells-how can i set by default the cell formatting

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)

### Sum by cell color:conditional formating rule

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.
Dragos

### Searching by cell format colour, then copying and pasting?

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

### How to sum up cells and multiply by 10, min 10 max 100?

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!

### Sum of cells colored using conditional format

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.

### Forcing cell format

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

### Sum Different Cells From Different Worksheets

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?

### Force cell format as text

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!

### Toolbar Button Images, Word Wrap, and Cell Format

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

### Summary Page in workbook, summing concantenated cells returns a 0 value.

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.

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.

### Copy And Paste Row By Cell Value Criteria

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

'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

'
'     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

```

### Function to sum same cell in multiple files

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

```
VB:
Range)

' Declare Functions
Dim rCell As Range
Dim rngFileName As Range
Dim vResult

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.

### Cell Formating Slows Down AutoFilter

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
"=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 ??