Sum by cell Color & Additional Criteria

[MarnieHocking]

Hi All

Can someone please help me keeping in mind that I don't use macros, VB etc.

I currently use the ASAP Utility to Sum by Cell Color

ie: =asapsumbycellcolor(B4:B8,B1) for Purple

ie: =asapsumbycellcolor(B4:B85,B2) for Green

however I would like to add another criteria to the formula

ie: =asapsumbycellcolor(B4:B5,B1) ONLY IF (A4:A5,A12) for Purple (total should be $30.00)

ie: =asapsumbycellcolor(B4:B5,B2) ONLY IF (A4:A5,A12) for Green (total should be $45.00)

Thanks in advance for ant help received
Marnie

	A	B
1		PURPLE	<Cell is Coloured
2		GREEN	<Cell is Coloured
3
4	Wayville / Aldinga	5.00	<Cell is Coloured Purple
5	Wayville / Aldinga	10.00	<Cell is Coloured Green
6	Wayville / Aldinga	15.00	<Cell is Coloured Green
7	Wayville / Aldinga	20.00	<Cell is Coloured Green
8	Wayville / Aldinga	25.00	<Cell is Coloured Purple
9
10
11		Totals
12	Wayville / Aldinga	30.00	Purple
13	Wayville / Aldinga	45.00	Green

<tbody>
</tbody>

 

[Kenneth Hobson]

Welcome to the forum!

=IF(asapsumbycellcolor(A4:A5,A12)=30, asapsumbycellcolor(B4:B5,B1) ,"")
The 2nd should be obvious.

 

[MarnieHocking]

Welcome to the forum!

=IF(asapsumbycellcolor(A4:A5,A12)=30, asapsumbycellcolor(B4:B5,B1) ,"")
The 2nd should be obvious.

Hi Kenneth

Not sure I've explained my query properly as
I don't understand what you are trying to say above :{

I have colored cells with values in them
I have text cells (not colored)
I want to sum the values of colored cells only if they match specific text cells

Hope this helps you help me

Cheers
Marnie

 

[Kenneth Hobson]

You lost me. Let me try a few one cell examples to see if I "get" what you want.

The sum of A1="ken" is 0. Do you want something like if B1="ken" AND [A1].Interior.ColorIndex =3 then sum the value in A1?

Most of the color routines just look at the manually set Interior.ColorIndex. Some can look at the case if either the manually set colorindex is a color OR a condtionalformat's true rule set the colorindex.

 

[MarnieHocking]

You lost me. Let me try a few one cell examples to see if I "get" what you want.

The sum of A1="ken" is 0. Do you want something like if B1="ken" AND [A1].Interior.ColorIndex =3 then sum the value in A1?

Most of the color routines just look at the manually set Interior.ColorIndex. Some can look at the case if either the manually set colorindex is a color OR a condtionalformat's true rule set the colorindex.

Hi Ken

Close ........

If the TEXT in A1="Ken" then sum the value in B1 IF it is a particular color

Cheers and thanks for your patience

 

[Kenneth Hobson]

'=mSumByColor(B35,B35:D35,H21:J21,"ken")
Function mSumByColor(CellColor As Range, rRange As Range, _
  mRange As Range, mVal) As Double
  Dim cSum As Double, cl As Range, i&, r As Range
  Dim Col&
  If rRange.Cells.Count <> mRange.Cells.Count Then Exit Function
  Col = CellColor.Interior.Color
  For Each cl In rRange
    i = i + 1
    If cl.Interior.Color = Col And mVal = mRange(i).Value Then
      cSum = WorksheetFunction.Sum(cl, cSum)
    End If
  Next cl
  mSumByColor = cSum
End Function