How to sum numbers in a range of a certain format?

[Fiona K]

Hi - I really hope you can help...

I have a table that lists multiple rows of projects across a timeline by day
(columns). For each project there is 4 stages. Each stage per day involves
a certain number of hours of work. My ultimate goal is to - create a way of
automatically summing the number of hours of work per project stage per day.
Knowing that in one day there is numerous projects at different stages. Is
there a formula or method that will enable me to do this???

My only thought has been to be able to create a formula that would
automatically sum numbers in a column 'per day' that matched a certain colour
or format (representing each project stage), but I cannot seem to work out a
SUM formula that would allow me to do this for each day.

Can you please tell me what I could do? I'm not very skilled at writing
macros, but can create them if you tell me what I need to do...

Many many thanks in advance and I hope this helps others as well

 

[FSt1]

hi
excel doen't have a built in formula for this but there is a custom function
that will.
from chip pearson's site.....http://cpearson.com/excel/colors.htm
The following function will return the sum of cells in a range that have
either an Interior (background) or Font of a specified colorindex. InRange
is the range of cells to examine, WhatColorIndex is the ColorIndex value to
count, and OfText indicates whether to return the ColorIndex of the Font (if
True) or the Interior (if False).
Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Double
'
' This function return the SUM of the values of cells in
' InRange with a background color, or if OfText is True a
' font color, equal to WhatColorIndex.
'
Dim Rng As Range
Dim OK As Boolean

Application.Volatile True
For Each Rng In InRange.Cells
If OfText = True Then
OK = (Rng.Font.ColorIndex = WhatColorIndex)
Else
OK = (Rng.Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(Rng.Value) Then
SumByColor = SumByColor + Rng.Value
End If
Next Rng

End Function

You can call this function from a worksheet cell with a formula like
=SUMBYCOLOR(A1:A10,3,FALSE)

to install this custom function, do this...
open the VB editor (ALT+F11)
on the tool bar.... click insert>module
past the above code into the module. done

you will need to know the colorindex numbers to use the fomula.
see this site.....
http://www.mvps.org/dmcritchie/excel/colors.htm
there is a table that you can copy and paste into an xlsheet.

you might want to visit chip's site. there is a lot of good stuff there.

Regards
FSt1

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.ColourCounter.html for a working
solution

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Herbert Seidenberg]

A better solution might be a Pivot Table.
No formulas, no macros, no colors.
If your layout matches, this might work:
http://www.freefilehosting.net/download/3abhb
Step through the Pivot Table Wizard for details.

 

[Fiona K]

Hi - thanks for your quick reply.

I have followed your steps, but I must be doing something wrong because
nothing is happening #NAME just appears. Can I just clarify what I had to do
to set this up in Excel?

1. Copy the code into a module
2. ColorIndex I just read off chip's table, e.g. red background color is 3
or is it #FF000 ?
2. Enter the formula =SUMBYCOLOR(range, index colour no., FALSE), e.g.
=SUMBYCOLOR(AM7:AM29,3,FALSE)

OR

1. Copy the code into a module
2. Enter formula above
3. Run as a macro? When I clicked 'Run' in the module screen in VB Editor
it kept coming up with a syntax error, highlighting the = in the 2nd line -
Optional OfText As Boolean = False) As Double

Sorry, I'm not that used to these things Am I missing a step?

 

[Fiona K]

Hi Herbert!

Thanks for creating the example for me off my file - I think this might be a
possible option. I just wanted to ask you if I can create the exact pivot
table you created, but the data source does not have the repeated project and
date column headings i.e. deleting rows 17, 18, 23,24 (so is row 12 at the
top). Is this possible?

 

[Gord Dibben]

Steps 1, 2, 2(sic) as below works for me.

The colorindex to enter is 3 as you have in the formula.

Are you sure you have the code in a General Module in the active workbook?


Gord Dibben MS Excel MVP

 

[Herbert Seidenberg]

You can delete those rows, but you have to
fill in the project column.
I added another page: Method 2
http://www.freefilehosting.net/download/3adm4