Conditional formatting

lfellows

New Member
Joined
Nov 22, 2019
Messages
35
HI,

I have some conditional formatting applied to a table with the formula:

=SUMIF($A$2:$A$499,$A1,$T$2:$T$499)>2272

Which highlights the rows in red.

This works perfectly however I want to advance it a bit more so that if the values in column "A" = 6, 7, 10, 11, 14, 15, 18, 19, 22, 23, 26, 27, 30, 31, 34, 35, 38, 39, 42, 43, 46, 47, 50, 51 and the total of column "T" is > 2272 highlight the row red.

And if values in column "A" = 8, 9, 12, 13, 16, 17, 20, 21, 24, 25, 28, 29, 32, 33, 36, 37, 40, 41, 44, 45, 48, 49 and the total in column "T" is > 1704 highlight the row red.

I hope this makes sense,

Thanks in advance!
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
822
Office Version
  1. 365
Platform
  1. Windows
When you say "the values in column A", which rows are you referring to?

Is this new condition in addition to the condition you showed above, or a replacement for it?
 

lfellows

New Member
Joined
Nov 22, 2019
Messages
35
Hi,

Apologies for the slow response:

So say for example you have multiple rows of data and the number in column "A" for each row is 6, and the numbers in column "T" vary. If the numbers in column "T" total to exceed 2272 I want to highlight all of the rows which have 6 in column "A" to be highlighted red.

I want this new condition to be a replacement of the condition I showed.

It must be some sort of sumif rule still but the middle condition to be a list of my numbers?

Cheers
Liam
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
822
Office Version
  1. 365
Platform
  1. Windows
I think you want to use similar logic as your original rule, but use two different thresholds to check the total of the values in column T, depending on which value is in column A.

So for each row, you want to look at the value in column A, and determine the total of the values in column T for all the rows having that same value in A, and highlight those where the total >2272. This applies only to those rows where the value of A is in this list: 6, 7, 10, 11, 14, 15, 18, 19, 22, 23, 26, 27, 30, 31, 34, 35, 38, 39, 42, 43, 46, 47, 50, 51

Then do the same thing, highlighting where the total of the values in T is >1704, where the value of A is in this list: 8, 9, 12, 13, 16, 17, 20, 21, 24, 25, 28, 29, 32, 33, 36, 37, 40, 41, 44, 45, 48, 49

Is that what you mean?
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
822
Office Version
  1. 365
Platform
  1. Windows
If I have described your spec properly then you want a rule that looks like this:

=SUMIF($A$2:$A$499,$A1,$T$2:$T$499)>IF(OR(A1={6,7,10,11,14,15,18,19,22,23,26,27,30,31,34,35,38,39,42,43,46,47,50,51}),2272,IF(OR(A1={8, 9, 12, 13, 16, 17, 20, 21, 24, 25, 28, 29, 32, 33, 36, 37, 40, 41, 44, 45, 48, 49}),1704))

I have not tried to reproduce your file and don't know what your data looks like, so I haven't tested it, but it is a valid formula. This can be simplified if the values in A are guaranteed to be in one of the two lists of values.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,663
Messages
5,626,166
Members
416,166
Latest member
Archimed

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top