Get values from colored cells

[Jan-Rikus]

Hi everyone

I hope someone can assist me.

I would like to get a formula that will give me values only of specific cells that are colored. For example, an IF statement entered into cell B1, that will give me the value entered into cell A1 only if A1 has a background fill. I would like to then extend this formula for the whole B column.

I hope it makes sense and that there is a formula for this problem.

[AliGW]

Welcome to the forum.

How have they been coloured? If there is a logic, then it can be done (but NOT using the cell's colour). If this is ENTIRELY manual, then you'll have to use VBA.

So, more information, please!

There are instructions at the top of the page explaining how to attach your sample workbook.

[Jan-Rikus]

Hi Ali

Thank you so much for getting back to me so soon. I really do appreciate it.

I have attached a sample of the spreadsheet I am working on. I have entered a very basic "IF" statement in brackets explaining what I would like to do.

I think it will definitely require VBA, but I am far from an expert.

I hope this is a bit more clearer.

Thank again Ali.

[AliGW]

In order to know, you need to tell me WHY those yellow cells are yellow - why have they been highlighted? What's the logic?

It's not the calculation itself that is the issue.

If, instead of highlighting, you added a column and placed an X next to the ones you want to calculate, then this would be easy peasy, of course.

[Jan-Rikus]

Oh ok, no it is completely manual. Those cells are yellow because those are our rental units that are empty. So now I need to calculate the total commission value of all units, but excluding the empty (yellow highlighted) units.

[AliGW]

OK - you have two simple choices:

1. Instead of highlighting, add a helper column:

Excel 2016 (Windows) 32 bit

	B	C	D	E
2	RENT [R]	COM [%])	EMPTY?
3	6690.00	8.0%		535.2
4	6290.00	8.0%	Y	0
5	5000.00	8.0%		400
6	5980.00	8.0%		478.4
7	5590.00	8.0%		447.2
8	5990.00	8.0%		479.2
9	5000.00	8.0%		400
10	6990.00	8.0%		559.2
11	6690.00	8.0%		535.2
12	7290.00	8.0%		583.2
13	6950.00	8.0%		556
14	6950.00	8.0%		556
15	7190.00	8.0%		575.2
16	5690.00	8.0%		455.2
17	7490.00	8.0%		599.2
18	9390.00	8.0%		751.2
19	6990.00	8.0%		559.2
20	7390.00	8.0%		591.2
21	7690.00	8.0%		615.2
22	6990.00	8.0%		559.2
23	8490.00	10.0%		849
24	8590.00	10.0%		859
25	8290.00	10.0%		829
26	8290.00	10.0%		829
27	7990.00	10.0%		799
28	6490.00	10.0%	Y	0
29	6490.00	10.0%		649
30	3990.00	10.0%		399
31	3990.00	10.0%		399

Sheet: 2022 EIENAARS

Excel 2016 (Windows) 32 bit

	E
3	=IF(D3="Y",0,B3*C3)

Sheet: 2022 EIENAARS

2. Use VBA - if this is your referred option, I can't help, but will move your thread to the correct section.

[KOKOSEK]

So, if it is manually coloured then you have to run manually macro after finish colouring.
Try this:

Please Login or Register  to view this content.

[Jan-Rikus]

The helper column will have to be done manually hey? I think I will definitely follow that method for now while I am still trying to get ahead of VBA.

Thank you so much for your assistance, I really do appreciate it Ali.

Have a great day

[Jan-Rikus]

I will try this thank you so much Kokosek. Will let you know if I manage to get it right!!

[AliGW]

The helper column will have to be done manually hey?

Yes, but your formatting has been done manually - is there any material difference? I am suggesting you use the helper column INSTEAD of the formatting. You could always add conditional formatting based on the helper column to highlight the cells so that they stand out, but this is not about that - it's about making your calculation possible.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[KOKOSEK]

As I understand from your note in original sample
"=If(cell C3 is yellow, then reflect the value of B3*C3 here, if not,0)"
yellow should determine multiplying, so if you decide to use formulas as AliGW advised, you have to change it into:

Please Login or Register  to view this content.

as it originally do oppositely.

[AliGW]

Good spot, however:

So now I need to calculate the total commission value of all units, but excluding the empty (yellow highlighted) units.

[KOKOSEK]

So this (what I've focused on)

"=If(cell C3 is yellow, then reflect the value of B3*C3 here, if not,0)"

is incorrect then.
So, main part change into:

Please Login or Register  to view this content.

[AliGW]

Thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.