# Sum by cell Color & Additional Criteria

#### MarnieHocking

##### New Member
Hi All

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)

Marnie

 A B 1 PURPLE

<tbody>
</tbody>

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Welcome to the forum!

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

Last edited:
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

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.

Last edited:
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

Code:
``````'=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``````

Replies
14
Views
380
Replies
3
Views
675
Replies
3
Views
222
Replies
3
Views
243
Replies
3
Views
424

1,203,485
Messages
6,055,688
Members
444,807
Latest member
RustyExcel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back