Ignore if cell in colour

[Shakes007]

Hi All,
Column A, As a sum to add all numerical data in that column. If any of the cells in the column are filled with colour, can they be excluded from the sum? Not sure this can be done but I may (hopefully am) wrong.
Thanks

 

[6StringJazzer]

There is no built-in function in Excel to return what fill color a cell has. So this would require a custom function written in VBA. Can you use VBA for this?

Also, are the cells colored manually, or are you using conditional formatting?

 

[Shakes007]

There is no built-in function in Excel to return what fill color a cell has. So this would require a custom function written in VBA. Can you use VBA for this?

Also, are the cells colored manually, or are you using conditional formatting?

Hi, I am using a conditional format for filling in the cells and I have never used VBA.

 

[Georgiboy]

As you are using conditional formatting for the colour, this can usually be built into a SUMIF function to create the sum you require. The easiest option to get help on this one would be to share here the formula you are using for the conditional formatting.

 

[Shakes007]

As you are using conditional formatting for the colour, this can usually be built into a SUMIF function to create the sum you require. The easiest option to get help on this one would be to share here the formula you are using for the conditional formatting.

Hi,
The Formula I am using in the conditional format is = =IF(ISTEXT,D33)

 

[6StringJazzer]

Deleted, I see you want to sum column A, based on what is in column D

 

[Georgiboy]

Maybe the below will do it?

Book1
	A	B	C	D
1	Nums			Test column
2	1			1
3	5			text
4	1			1
5	7			text
6	1			1
7	1			1
8	1			1
9	1			text
10	1			1
11	1			1
12	1			1
13	1			1
14
15	13
Sheet2

Cell Formulas
Range	Formula
A15	A15	=SUM(ISTEXT(D2:D13)*A2:A13)

 

[Shakes007]

Maybe the below will do it?

Book1
	A	B	C	D
1	Nums			Test column
2	1			1
3	5			text
4	1			1
5	7			text
6	1			1
7	1			1
8	1			1
9	1			text
10	1			1
11	1			1
12	1			1
13	1			1
14
15	13
Sheet2

Cell Formulas
Range	Formula
A15	A15	=SUM(ISTEXT(D2:D13)*A2:A13)

I will try to explain with added snippet. The green is added to the 1st column when I add the date of payment in 2nd column. The 1st column as a sum to show total. What I would like is when I have paid the bill and the column (cell) goes to Green, that payment it is removed from the total. This would then just sum remaining owed for that month

 

[Georgiboy]

Maybe we could just replace ISTEXT with ISBLANK:

Book1
	C	D
1	Nums	Test column
2	£1.00	04/12/2023
3	£5.00	05/12/2023
4	£1.00	06/12/2023
5	£7.00	07/12/2023
6	£1.00	08/12/2023
7	£1.00
8	£1.00
9	£1.00
10	£1.00
11	£1.00
12	£1.00
13	£1.00
14
15	£7.00
Sheet2

Cell Formulas
Range	Formula
C15	C15	=SUM(ISBLANK(D2:D13)*C2:C13)

 

[Georgiboy]

Or even a SUMIF formula:

Book1
	C	D
1	Nums	Test column
2	£1.00	04/12/2023
3	£5.00	05/12/2023
4	£1.00	06/12/2023
5	£7.00	07/12/2023
6	£1.00	08/12/2023
7	£1.00
8	£1.00
9	£1.00
10	£1.00
11	£1.00
12	£1.00
13	£1.00
14
15	£7.00
Sheet2

Cell Formulas
Range	Formula
C15	C15	=SUMIF(D2:D13,"",C2:C13)