Conditional formatting for values in a column that exceed a set limitwhen summed

[jaggy]

Hi,

I'm looking for an approach that will let me sum up values in a column and then conditionally format the first value of the remaining values in a column that has exceeded a preset limit.

In other words, I have a list of tasks with estimates that I would like to compare against a limit to know how many tasks I can complete before I hit this limit. I would like to do this dynamically because the list will change periodically and I would like to know immediately when I have hit my limit.

Any help would be greatly appreciated.

-jaggy

 

[Claus Busch]

Hi Jaggy,

Am Wed, 31 Dec 2014 13:51:42 -0800 (PST) schrieb jaggy:

I'm looking for an approach that will let me sum up values in a column and then conditionally format the first value of the remaining values in a column that has exceeded a preset limit.

your values in column D, the linit in G1
Then select column D and use the following formula for the conditional
formatting:
=COUNTIF($D$11,">"&$G$1)=1


Regards
Claus B.

 

[jaggy]

Hi,

I'm looking for an approach that will let me sum up values in a column and then conditionally format the first value of the remaining values in a column that has exceeded a preset limit.

In other words, I have a list of tasks with estimates that I would like to compare against a limit to know how many tasks I can complete before I hit this limit. I would like to do this dynamically because the list will change periodically and I would like to know immediately when I have hit my limit.

Any help would be greatly appreciated.

-jaggy

Hi Claus,

Thanks for the quick reply. I tried the formula provided but it gives a count of values in the column. I am looking for excel to sum all values in asingle column until it hits a predetermined total. I am looking to see how many tasks can be accomplished before we max out.

BTW - I tried SUMIF as well. No luck.

jaggy

 

[Claus Busch]

Hi Jaggy,

Am Mon, 5 Jan 2015 14:12:03 -0800 (PST) schrieb jaggy:

I tried the formula provided but it gives a count of values in the column. I am looking for excel to sum all values in a single column until it hits a predetermined total. I am looking to see how many tasks can be accomplished before we max out.

sorry. my bad.

Make a helper column and write in first row:
=SUM($D$11)>=$G$1
and copy down. The first occurance of TRUE is greater than G1.
Or try it the opposite way.
Select D2n and write in Conditional Formatting:
=D2+SUMME($D$11)<$G$1
and select a format. Now the cells with sum < g1 are highlighted


Regards
Claus B.

 

[jaggy]

Hi Jaggy,

Am Mon, 5 Jan 2015 14:12:03 -0800 (PST) schrieb jaggy:


sorry. my bad.

Make a helper column and write in first row:
=SUM($D$11)>=$G$1
and copy down. The first occurance of TRUE is greater than G1.
Or try it the opposite way.
Select D2n and write in Conditional Formatting:
=D2+SUMME($D$11)<$G$1
and select a format. Now the cells with sum < g1 are highlighted


Regards
Claus B.

Thanks Claus - that worked perfectly.