Sum Conditional Formatting Cells

[NVRensburg]

Please could someone have a look at whyyyyyyyyy my formula isn't working.....it's keeps giving me the #NAME? error when using the =SumConditionColorCells formula. Help would be greatly appreciated!!!!

Test Accs.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Acc A									Acc B
2	Tx Date	Proc Date	Type	Details	Particulars	Code	Reference	Amount		Tx Date	Proc Date	Type	Details	Particulars	Code	Reference	Amount
3										20 Aug 2020			1				-$48.61
4										19 Aug 2020			2				-$41.49			Barfoot and Thompson Rentals	#NAME?
5										19 Aug 2020			3				-$21.14
6										19 Aug 2020			4				-$17.94
7										19 Aug 2020			5				-$17.74
8										18 Aug 2020			Barfoot & Thompson			Rent	-$550.00
9										19 Aug 2020			6				$32.91
10										18 Aug 2020			7				$769.39
11										17 Aug 2020			8				-$27.98
12										14 Aug 2020			1				-$20.99
13										13 Aug 2020			2				$100.00
14										13 Aug 2020			5				-$54.06
15										13 Aug 2020			1				-$39.20
16										13 Aug 2020			2				$8.34
17										13 Aug 2020			54				$4.54
18										12 Aug 2020			1				-$38.64
19										12 Aug 2020			2				-$38.05
20										11 Aug 2020			2				-$65.00
21										11 Aug 2020			Barfoot & Thompson			Rent	-$550.00
22										11 Aug 2020			1				-$50.00
23										11 Aug 2020			2				-$20.00
24										11 Aug 2020			7				$781.39
25										10 Aug 2020			41				-$59.00
26										10 Aug 2020			52				-$24.70
27										09 Aug 2020			7				-$21.50
28										10 Aug 2020			1				-$20.00
29										10 Aug 2020			12				-$19.50
30										10 Aug 2020			4				-$13.70
31										10 Aug 2020			4				-$13.24
33										10 Aug 2020			12				-$7.35
34										07 Aug 2020			54				-$28.87
35										07 Aug 2020			4				$200.00
36										06 Aug 2020			1				-$58.70
37										06 Aug 2020			2				-$35.00
38										06 Aug 2020			4				-$12.90
39										05 Aug 2020			45				-$20.00
40										05 Aug 2020			Barfoot & Thompson			Rent	-$550.00
41										05 Aug 2020			4				-$20.00
42										05 Aug 2020			7				$95.05
43										04 Aug 2020			4				-$125.22
44										04 Aug 2020			52				-$39.00
45										04 Aug 2020			4				-$3.76
46										04 Aug 2020			52				$790.70
47										03 Aug 2020			4				-$33.77
48										03 Aug 2020			2				-$25.25
49										03 Aug 2020			274				-$20.00
50										03 Aug 2020			24				-$15.00
51										03 Aug 2020			2				-$7.60
52										31 Jul 2020			45				-$13.80
53										30 Jul 2020			4				-$101.83
54										30 Jul 2020			5				-$54.06
55										30 Jul 2020			12				-$5.93
56										30 Jul 2020			Barfoot & Thompson			Rent	-$550.00
57										29 Jul 2020			1				-$16.50
58										29 Jul 2020			24				-$16.50
59										29 Jul 2020			54				-$14.20
60										29 Jul 2020			5				-$10.00
61										29 Jul 2020			12				-$20.00
62										28 Jul 2020			12				-$0.02
63										28 Jul 2020			1545				$907.95
64										27 Jul 2020			45				-$68.19
65										27 Jul 2020			2				-$43.47
66										27 Jul 2020			1				-$20.60
67										26 Jul 2020			21				-$82.50
68										24 Jul 2020			5				-$74.50
69										24 Jul 2020			5				-$16.50
70										24 Jul 2020			412				-$15.91
71										24 Jul 2020			5				-$10.00
72										23 Jul 2020			Barfoot & Thompson			Rent	-$550.00
73										23 Jul 2020			4				-$100.00
74										23 Jul 2020			24				-$308.06
75										22 Jul 2020			54				-$100.00
76										22 Jul 2020			5				$755.00
SEPT 2020

Cell Formulas
Range	Formula
U4	U4	=SumConditionColorCells(Q3:Q76,T4)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J3:Q499	Expression	=AND($M3="Barfoot & Thompson", $P3="Rent")	text	YES

 

[etaf]

have you created a user defined function called SumConditionColorCells
you could use a COUNTIFS()
based on the conditional formatting formula
=AND($M3="Barfoot & Thompson", $P3="Rent")
Countifs(M:M,"Barfoot & Thompson", P:P, "Rent")

 

[NVRensburg]

I don't really want to do that as I'm going to be using different colours for different items....just hoping to get the formula sumcellsbycolour to work....

 

[etaf]

there is no function in excel called

sumcellsbycolour
or
SumConditionColorCells

you will need to either do this by VBA or setup a UDF User Defined Function to do that.

NOT my expertised, perhaps close this thread and start a new one , UDF or VBA to SUM Cell Colours - and specify exactly what you want to sum, colours etc

have a read here

How to count by color and sum by color in Excel 2016, 2019, and 2013

A quick way to count cells by color in Excel and get the sum of colored cells in Excel 2019, Excel 2016 and Excel 2013.

www.ablebits.com

.

Sum Cells based on Background Color

In this tutorial, we are going to see how to add the contents of certain cells based on their background colors and we are going to do this by using SUMIF.

www.exceltrick.com