Conditional formatting by using formula

[Hrishi]

Hello, I have few figures arranged in row. In columns 1 i have a figure for reference and I want to highlight figures from columns number 2 to 9 in such a way that summation of those highlighted figures from columns 2 to column under consideration shall be less than figure in column number 1 i.e. 30.

I tried to use sum($2:3)<$1, but ist not working, and if i drag it on right column number 3 in formula is not dragging along.

[TABLE="class: grid, width: 500, align: left"]
<colgroup><col width="98"><col width="85" span="8"><col width="85"><col width="85"></colgroup><tbody>[TR]
[TD="width: 85"]1
[/TD]
[TD="width: 85"]2
[/TD]
[TD="width: 85"]3
[/TD]
[TD="width: 85"]4
[/TD]
[TD="width: 85"]5
[/TD]
[TD="width: 85"]6
[/TD]
[TD="width: 85"]7
[/TD]
[TD="width: 85"]8
[/TD]
[TD="width: 85"]9
[/TD]
[/TR]
[TR]
[TD="width: 85"] - 30
[/TD]
[TD="width: 85"] 5
[/TD]
[TD="width: 85"]10
[/TD]
[TD="width: 85"]5
[/TD]
[TD="width: 85"]3
[/TD]
[TD="width: 85"]3
[/TD]
[TD="width: 85"]5
[/TD]
[TD="width: 85"]5
[/TD]
[TD="width: 85"]3
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

This is somewhat unclear.
You want to highlight all the figures from column 2 to 9 (B to I) if the sum of their toal is less than the figure in column 1 (A) ?

Select all the columns 2 to 9 (B to I) FOR EACH ROW IN THE TABLE (I suspect there are other rows)

Conditional Formatting
New Rule
Use a formula to determine...

=SUM($B1:$F1)<$A1

Format as required

 

[Marcelo Branco]

Is this the expected result?


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][td]

4​

[/td][td]

5​

[/td][td]

6​

[/td][td]

7​

[/td][td]

8​

[/td][td]

9​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

30​

[/td][td="bgcolor:#FFFF00"]

5​

[/td][td="bgcolor:#FFFF00"]

10​

[/td][td="bgcolor:#FFFF00"]

5​

[/td][td="bgcolor:#FFFF00"]

3​

[/td][td="bgcolor:#FFFF00"]

3​

[/td][td]

5​

[/td][td]

5​

[/td][td]

3​

[/td][/tr]
[/table]

If so, select B2:I2
Home > Conditional Formatting > New Rule > Use a formula...
insert this formula
=SUM($B2:B2)<$A2
Pick the format you want (Fill yellow in the example)

M.

 

[Hrishi]

Yes This is what is expected. However its not showing result to me when I tried several times. I used same formula. Can you plz look into it. I am pasting image here to show how i tried. let me know if you can see image.

 

[Special-K99]

FYI

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

 

[Hrishi]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]3
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]30
[/TD]
[TD]5
[/TD]
[TD]10
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]






I selected G5 to N5 cells, then in formula window in conditioning format I typed formula sum($G4:G4)<$F4, then customized format for cells

Still its not working. i tried in several files as well. snapshot is linked here. https://drive.google.com/open?id=1rf934P1t1CIUsrgYimPj9SLw27IBn5Nt

 

[nikio8]

your formula says $G4:G4

you should always go from 1, conditional format is very buggy.
=$F1<sum($g1:$n1)
< SUM($G1:$N1)
manage rules
expand the range for whole worksheet, range G:N, check if F1 is still F1 and not F1045646</sum($g1:$n1)

 

[Hrishi]

I guess you have interpreted differently. I don't want to highlight F4 from above table. I want to highlight cells from G4 onward till N4, that too only those cell if sum if those cells is less than F4 i.e. 30 in above tale. so as per table in previous thread my formatting formula should highlight G4 to L4.

 

[Peter_SSs]

I selected G5 to N5 cells, then in formula window in conditioning format I typed formula sum($G4:G4)<$F4, then customized format for cells

Still its not working. i tried in several files as well. snapshot is linked here. https://drive.google.com/open?id=1rf934P1t1CIUsrgYimPj9SLw27IBn5Nt

I suspect that you initially did not quite enter the formula correctly. If you look at the formula in the image you linked to, you will see it actually shows

="SUM($G4:G4)<$F4"

Go back in to the CF dialog and choose manage Rules & edit the rule to remove those quote marks in it.

 

[Hrishi]

Oh I see, I had typed Sum($G4:G4)<$F4 and entered then excel put sign of = and " quotes on its own. so i thought its default correction by excel. so i didn't try to remove it.
Thanks a lot sir. Now it worked as I wanted. A big Thank to you.