# 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

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\$11,">"&\$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\$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.

Ad

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\$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.

## 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.