Sum by color background and if another condition

[Sonic47]

Hello,

I am trying to write/find a correct vba code able to sum the amount in a colored cell based on two conditions : the color of the background and based on another condition (here the fruit)
You can find a easy table showing the situation :
I want the formula to sum the amount in the cell based on the color yellow and if the fruit if an apple for exemple.
We can see the result below, but made manually.

I found a lot of code able to sum (without second condition) or code able to see the color code of the background, but I am not able to use "SUMIF" with it able to choose based on the fruit directly.

Could you please help me ?

Thank you very much.

 

[Sonic47]

I found this very useful link but there is no solution for my problem :

Color Functions In Excel

www.cpearson.com

 

[MARK858]

Can you post your table using the boards XL2BB app please as we can't copy/paste your image into Excel cells (the XL2BB icon in the reply window is a link to the instructions and the download for XL2BB).

 

[Sonic47]

Proje Maliyet Öngürü.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1		January	February				Yellow				Blue
2	apple	1	6				January	February			January	February
3	banana	25	25			Apple	1	9		Apple	5	0
4	mango	80	2			Banana	0	0		Banana	30	28
5	apple	5	3			Mango	80	2		Mango	0	0
6	banana	5	3
Sheet1

Cell Formulas
Range	Formula
G3,G5:H5	G3	=B2
H3	H3	=C2+C5
K3	K3	=B5
K4:L4	K4	=B3+B6

 

[mikerickson]

You could use this UDF in combination with SUMPRODUCT
=SUMPRODUCT(--(A2:A6="apple)*(ColorOf(B2:C6=65535)*(B2:C6))

Like all color reading functions, it does not calculate when the user changes a cell's color, only when the user enters a value in any cell.

Function ColorOf(aRange As Range) As Variant
    Dim Result As Variant
    Dim i As Long, j As Long
    Application.Volatile
    Result = aRange.Value
    For i = 1 To UBound(Result, 1)
        For j = 1 To UBound(Result, 2)
            Result(i, j) = aRange.Cells(i, j).Interior.Color
        Next j
    Next i
    ColorOf = Result
End Function

 

[Sonic47]

Hello, thank you very much Mikerickson, but unfortunaterly in the document I must prepare, my colleague will change the colors of the cells.
I tried your code but I get an error for that .. I am trying to understand why.

 

[mikerickson]

Your colleague will change the colors and the cells will reflect faulty results until they enter something in some cell.
And there is no way around that. VBA solutions don't work because changing a cells color do not trigger a Change event. UDF base solutions don't work, because changing a cell's color does not trigger calculation. The only way that your colleague will be sure of the results of any approach will be for him to force calculation (or call a macro if you go that direction).

The SUMPRODUCT formula that I posted was missing a quoteation mark. Ishould be put in G3. It should also be altered to
=SUMPRODUCT(--($A$2:$A$6=$F3)*(ColorOf(B$2:B$6=65535)*(B$2:B$6))

 

[Sonic47]

Yes I have just tried and it works fine !
Thank you very much for your support

 

[chrispen]

Appreciate I am resurrecting a 3yo post... but I need this exact function.... However I'm getting #VALUE error when replicating the above setup exactly.... can anyone help?
Any assistance appreciated. Have uploaded my mainsheet using Xl2bb

apple.xlsm
	A	B	C	D	E	F	G	H
1		January	February				Yellow
2	apple	1	6				January	February
3	banana	25	25			apple	#VALUE!
4	mango	80	2			banana	#VALUE!
5	apple	5	3			mango
6	banana	5	3
7
8
9
Sheet1

Cell Formulas
Range	Formula
G3	G3	=SUMPRODUCT(--(A2:A6="apple")*(ColorOf(B2:B6=65535)*(B2:B6)))
G4	G4	=SUMPRODUCT(--($A$2:$A$6=$F3)*(ColorOf(B$2:B$6=65535)*(B$2:B$6)))

 

[Fluff]

Try it like

=SUMPRODUCT(--($A$2:$A$6=$F2)*(ColorOf(B$2:B$6)=65535)*(B$2:B$6))