Tricky..........

[buntykins]

In Row 1 of my column I have total amount of customers, in row 2 I have total amount of calls and in row 3 I have the date. The date row cells are coloured differently depending on what agent worked. There is no discernible pattern to this colouring.

I need a formula that will look along the coloured row and sum the total mount of customers (Row 1) for each colour. So basically, it's like a SUMIF but the criteria is colour. Is this possible or will I need to go into VBA?

And if I do need to go into VBA, what do I do?

Thanks for your help

Janie

xx

[sen_edp]

TRY A UDF BY ERLADSEN

http://www.erlandsendata.no/english/...sumbycolor.htm

[buntykins]

Cool thanks, except I dont want to add the coloured cells together, I want to add the ones above it. Also, how would I make this work? Where would I put it? How would I make it run?

Janie

[Ivan F Moala]

On 2002-05-14 05:09, buntykins wrote:

In Row 1 of my column I have total amount of customers, in row 2 I have total amount of calls and in row 3 I have the date. The date row cells are coloured differently depending on what agent worked. There is no discernible pattern to this colouring.

I need a formula that will look along the coloured row and sum the total mount of customers (Row 1) for each colour. So basically, it's like a SUMIF but the criteria is colour. Is this possible or will I need to go into VBA?

And if I do need to go into VBA, what do I do?

Thanks for your help

Janie

xx

Hi Janie, if I understand you correctly then
you have something like this;

Microsoft Excel - Book4_______________Running: xl2000 : OS = Windows (32-bit) 4.10
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
E3		=	=SumByColour(C3,C$2:C$15)
*	A	B	C	D	E
1	Customers	Calls	Date	Result	User Defined Function
2	16	1	02/01/02	:alert("=SumByColour(C2,C$2:C$15)")>27	=SumByColour(C2,C$2:C$15)
3	1	5	02/02/02	:alert("=SumByColour(C3,C$2:C$15)")>27	=SumByColour(C3,C$2:C$15)
4	5	11	02/03/02	:alert("=SumByColour(C4,C$2:C$15)")>17	=SumByColour(C4,C$2:C$15)
5	6	5	02/04/02	:alert("=SumByColour(C5,C$2:C$15)")>49	=SumByColour(C5,C$2:C$15)
6	9	1	02/05/02	:alert("=SumByColour(C6,C$2:C$15)")>16	=SumByColour(C6,C$2:C$15)
7	45	2	02/06/02	:alert("=SumByColour(C7,C$2:C$15)")>47	*
8	8	16	02/07/02	:alert("=SumByColour(C8,C$2:C$15)")>17	*
9	1	9	02/08/02	:alert("=SumByColour(C9,C$2:C$15)")>27	*
10	9	10	02/09/02	:alert("=SumByColour(C10,C$2:C$15)")>27	*
11	12	4	02/10/02	:alert("=SumByColour(C11,C$2:C$15)")>17	*
12	43	2	02/11/02	:alert("=SumByColour(C12,C$2:C$15)")>49	*
13	7	9	02/12/02	:alert("=SumByColour(C13,C$2:C$15)")>16	*
14	2	7	02/13/02	:alert("=SumByColour(C14,C$2:C$15)")>47	*
15	9	8	02/14/02	:alert("=SumByColour(C15,C$2:C$15)")>17	*
Sheet4

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.23]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


What you need to do is setup a UDF
User defined Function as suggested by Andreas

The UDF in your case requires something a
little different as you are not counting
colour cells but Column 1 cells ?? See above.

Follow these instructions to get the UDF in
1) Press Alt + F11 - takes you into the VBA Editor
2) Press Ctrl + 4 - takes you to the Project explorer
3) Right clcik on any of the Objects > Insert > Module
4) Cut & Paste this Code


UDF:

Function SumByColour(CellColour As Range, SumRange As Range)

Dim cell As Range

Dim SumColour As Double

Dim MySum



Application.Volatile

'If CellColour.Cells > 1 Then Exit Function

SumColour = CellColour.Interior.ColorIndex



For Each cell In SumRange

    If cell.Interior.ColorIndex = SumColour Then

        MySum = MySum + cell.Offset(0, -2)

    End If

Next cell

SumByColour = MySum



End Function

If you require further then just Post

[buntykins]

Ivan,

Thanks that's great. So basically I've opened a module and pasted that in? So now when I want to use it, what do I do?

Thanks

Janie
xxxxx

[sen_edp]

Hello Janie,

Go to the menus-insert function-function category-user defined-function name-sumbycolour

and put it is as Ivan shows in his sheet in the appropriate cell.

[Ivan F Moala]

On 2002-05-14 05:51, buntykins wrote:
Ivan,

Thanks that's great. So basically I've opened a module and pasted that in? So now when I want to use it, what do I do?

Thanks

Janie
xxxxx

If you have a look @ the Post I sent you will
see I have the UDF setup in Column D (Results) and the Formula next to it E.
eg in D2

=SumByColour(C2,C$2:C$15)
Where C2 is the Colour to search for
C$2:C$15 is the Colour Range

The UDF searchs the colour range for a match
on C2 and then Looks @ Column A and adds this
up.


Note: The UDF is Avail to you via the normal
Paste Function button > under User Defined.

Post soon if you still need help...BUT
just look @ the Posted example above....
sleep time soon

[buntykins]

Oh you absolute honey!!

Thanks, it works!

Janie

xxxxxxxx
xxxxxx