Conditional formatting

[lfellows]

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!

 

[6StringJazzer]

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]

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]

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]

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.