Various items where I need to sum a column where another column is green

[Wilcock1]

Please can you advise me on how I can sum a column of numbers (C) which have a specified criteria (A) and background colour in another cell on the same row (B) and when sum the value of another cell. The colour is manually entered not conditional formatted.

A B C
1 green formatted cell 100
1 purple formatted cell 200
1 blue formatted cell 400
1 green formatted cell 200
1 purple formatted cell 500
1 blue formatted cell 100
1 green formatted cell 200
1 purple formatted cell 300
1 blue formatted cell 400

Answer green = 500
Answer purple = 1000
Answer blue = 900

Your help would be much appreciated

 

[oldbrewer]

if the color is manually entered, there is presumably a rule to define the color

if so use that rule to place a label in a help
er column

 

[Johnny C]

Assuming if the colours are manually done, you can't formularise the conditions for the colour - I'm thinking bank account reconciliations which brings back some bad memories

In another column enter the following:
=GET.CELL(63,Sheet1!B2)
or whatever cell the colour needs to be checked in. This returns a value for the colour.
You can then use SUMIFS/COUNTIFS etc as required based on the value

You may need to hit F9 as Get.Cells can be pernickity with colours if they change as Excel doesn't think a colour change necessarily means a calculation is required so may not do it automatically.
You could add some code so when you select a different cell it's automatically calculated but you may as well hit F9

Here is a reference for a list of cell fill colors
http://www.smixe.com/excel-color-pallette.html
(I presume the link still works, it's blocked on my work PC)


HTH

 

[Wilcock1]

Hi Johnny, sorry for the delay in replying. That worked and your reply is very much appreciated! Thanks very much, Andrew

 

[Johnny C]

Glad I could help )