Sum value if another cell colour is (green / red)

[nparsons75]

I would like to calculate the sum of values if their adjacent cell is a particular colour. Hope you can help....

In the example below, I would like to sum all values if the cell to the left is green, same for the reds.

Q3 Outlook TV.xlsx
	F	G
57	g	26,296
64	g	23,690
65	g	23,691
86	r	16,828
87	r	14,582
109	g	0
110	g	0
111	g	0
Wheelsets - Q3 JUL

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F9:F160	Cell Value	="G"	text	NO
F9:F160	Cell Value	="A"	text	NO
F9:F160	Cell Value	="R"	text	NO

 

[etaf]

in the cells , conditional formatting seems to be if the cell contains a G or A or R

if so then you could use a SUMIF() using those codes in the range F9:F160

For a Green SUM = SUMIF(F9:F160, "G", G6:G160)

otherwise , am i misunderstanding

 

[nparsons75]

Thats perfect and works great, I have however just realised I have missed something. The data is in a fliterable table, I need to be able to filter and the sumifs to also work as a subtotal when filtering. Is this possible?

 

[etaf]

not sure where you applying the FILTER -
SUBTOTAL() will SUM a filtered list

=SUBTOTAL(109,G9:G160)

 

[nparsons75]

So the filter is the drop down in a column. For example, column A includes a list of customers, I select the drop down filter and choose customer 1. I would like to see the sumifs work now for only the filtered selection, to do this I need to somehow combine the sumif and subtotal? I think....

 

[etaf]

OK, So when you have the customer 1 , ther would be G , A & R and you want a SUM of each
I'm NOT sure , as that may need a sumproduct with offset
What version of Excel do you have ?
Add to your profile

 

[nparsons75]

2016 I think.....maybe slightly later (work version), currently on 365 at home. I have read something about offset but blew my mind!

 

[nparsons75]

This is a small example.....

When filtering on customer, the sum does not change

Book1
	C	D	E	F	G
5					sum
6	customer 1	r	5321		7829
7	customer 1	r	55
8	customer 1	r	6689
9	customer 2	g	415
10	customer 2	g	266
11	customer 2	g	6898
12	customer 2	a	562
13	customer 2	a	3336
14	customer 3	a	2151
15	customer 3	g	25
16	customer 4	g	225
17	customer 4	a	22265
Sheet1

Cell Formulas
Range	Formula
G6	G6	=SUMIFS(E6:E17,D6:D17,"g")

 

[etaf]

=SUMPRODUCT(SUBTOTAL(3,OFFSET(F9:F160,ROW(F9:F160)-MIN(ROW(F9:F160)),,1)),(F9:F160="G")*(G9:G160))

IF you want to SUM all Visible , then you can use SUMPRODUCT()

BUT if you want G , R , A as separate SUMS

NOT 100% certain on this formula - MAY be worth closing this thread and starting a new one with the new question
based on this

Excel - Sum of filtered data with criteria

I have an Excel sheet that is essentially a report on steroids by manipulating filters. The sheet has nearly 700 subtotals within the various columns. The columns all must have filters. My subtotals

answers.microsoft.com

Book1
	E	F	G	H
8				11
9	Customer1	g	1
13	Customer1	A	10000
16	Customer1	g	10
Sheet1

Cell Formulas
Range	Formula
H8	H8	=SUMPRODUCT(SUBTOTAL(3,OFFSET(F9:F160,ROW(F9:F160)-MIN(ROW(F9:F160)),,1)),(F9:F160="G")*(G9:G160))