EXPERT HELP REQUIRED - Specific query on Conditional Sum / Custom Function

[Essonc]

Can any one offer any assistance with the following query.

See example table in a worksheet;


Column
A B C D E F
Seq Month C/C A/C Ac name Actual £
1 Jun 04 7313 30613 Training(NR) £2,250
2 Jun 04 7323 60301 Plant Hire £5,500
3 Jul 04 7324 30810 Prot clthng £1,500
4 Aug 04 7313 30612 Training (R) £6,500
ETC....

NOTES: -The data at the moment extends to 600 line items for one mont
therefore potentially could be circa 7,500 line items
-I dont want to use pivot tables as this is to be
template to issue to other users with little or no pivot experiance.

I need to pull this information into another summary worksheet, b
colour, by month and by C/C as such;

SUMMARY 7313
Category Jan 04 Feb 04 Mar04......................Dec 04
Purple 1
Blue 2
Yellow 3
Green 4
Dark Green 5
Grey 6
Salmon 7


SUMMARY 7323
Category Jan 04 Feb 04 Mar 04 ....................Dec 04
Purple 1
Blue 2
Yellow 3
Green 4
Dark Green 5
Grey 6
Salmon 7

NOTE: There are 14 seperate C/C summary sections within this on
worksheet


What I have done so far is to add a column G with a vlookup showing th
colour allocated per Acc code based on the array table below;


Acc Code Colour
20330 prpl
20502 prpl
30103 prpl
30703 prpl
30613 Gry
30622 Gry
30702 Gry
30810 Salm
30811 Gry
30820 Gry
30830 prpl
30850 Gry
60301 Yllw
60311 Yllw
60401 Yllw
60420 Dk Green
60421 Yllw
60425 Yllw
61160 Yllw
85118 Green

Giving;

A B C D E F G
Seq Month C/C A/C Ac name Actual £ Colour
1 Jun 04 7313 30613 Training(NR) £2,250 Gry
2 Jun 04 7323 60301 Plant Hire £5,500 Yllw
3 Jul 04 7324 30810 Prot clthng £1,500 Salm
4 Aug 04 7313 30612 Training (R) £6,500 Gry
ETC....

I have tried to do it via the 'conditional sum' function to sum if a
entry is in Jun 04, is C/C 7313 and is 'prpl' ( colour purple) and i
tells me that the formula you are buidling is too long. Please specif
fewer conditions. If I do it with only the two conditions to check i
it works I get the right answer,...however if you click on the fomul
bar at the top the array brackets disappera and the value goes all t
pot, likewise if you double click on the cell where the formula i
located.


Any suggestions?, bearing in mind that I am not really any use o
Visual Basic, but did manage this with help as a custo
function.......and YES it doesnt work either;

Function Customif(Range1 As Range, Col_num As Integer, Criteria A
String, Col_num2 As Integer, Criteria_2 As String, Col_num3 A
Integer)

Dim rw
Dim Result

For Each rw In Range1.Rows

If rw.Columns(Col_num) = Criteria And rw.Columns(Col_num2)
Criteria2 Then

Result = Result + rw.Columns(Col_num3)

End If

Next rw

Customif = Result

End Function


Any assistance greatly appreciated

Coli

 

[Frank Kabel]

Hi
IMHO it would be easier to use a pivot table. But for
conditional sum formulas see:

-----Original Message-----

Can any one offer any assistance with the following query.

See example table in a worksheet;


Column
A B C D E F
Seq Month C/C A/C Ac name Actual £
1 Jun 04 7313 30613 Training(NR) £2,250
2 Jun 04 7323 60301 Plant Hire £5,500
3 Jul 04 7324 30810 Prot clthng £1,500
4 Aug 04 7313 30612 Training (R) £6,500
ETC....

NOTES: -The data at the moment extends to 600 line items for one month
therefore potentially could be circa 7,500 line items
-I dont want to use pivot tables as this is to be a
template to issue to other users with little or no pivot experiance.

I need to pull this information into another summary worksheet, by
colour, by month and by C/C as such;

SUMMARY 7313
Category Jan 04 Feb 04

Mar04......................Dec 04