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


J

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
 
Ad

Advertisements

C

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$1:D1,">"&$G$1)=1


Regards
Claus B.
 
J

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
 
C

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$1:D1)>=$G$1
and copy down. The first occurance of TRUE is greater than G1.
Or try it the opposite way.
Select D2:Dn and write in Conditional Formatting:
=D2+SUMME($D$1:D1)<$G$1
and select a format. Now the cells with sum < g1 are highlighted


Regards
Claus B.
 
Ad

Advertisements

J

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$1:D1)>=$G$1
and copy down. The first occurance of TRUE is greater than G1.
Or try it the opposite way.
Select D2:Dn and write in Conditional Formatting:
=D2+SUMME($D$1:D1)<$G$1
and select a format. Now the cells with sum < g1 are highlighted


Regards
Claus B.

Thanks Claus - that worked perfectly.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top