Conditional Formatting to Check if the Sum of a Range of Cells is Less Then 20

  • Thread starter Thread starter Minitman
  • Start date Start date
M

Minitman

Greetings,

I have 2 columns - Date and Number of Items. I need to see at a
glance if the number of items for any date is greater then 20 items.
If it is I need the cells from that set of numbers (the ones for that
date) to turn a color to flag the overage.

I have done some work with Conditional Formatting, but I can't see how
to do a SUM of only those numbers that fall on the same day as the
date cell is for that row.

example:

Date | # of Items
06/04/04| 10
| 3
| 6
06/05/04| 12
| 9
06/07/04| 9
| 10
Etc.

Since the totals for 06/04/04 and 06/07/04 are less then 20, I need
no color change, However, the total for 06/05/04 is greater then 20,
so I would need to have both of the cells that contain the numbers to
change color.

I don't know if this is even possible.

If it is, could someone please show me how to accomplish this?

If not, is there any work around that might give similar results?

TIA

-Minitman
 
As long as the date was copied down, not as you show, then this would work
in the Items cells, CF with a formula of

= SUMIF($A$1:$A$100,A1,$B$1:$B$100)>20

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hey Bob,

I made the change you mentioned and tweaked it for my app and it works
great - Thank You

-Minitman
 
Back
Top