Excel 2003 - Adding up cells that contain Conditional Formatting

F

FlorencePS

Hi. I've got a spreadsheet that has conditional formatting in one column as
follows:

1-14 days - green
15-30 days - yellow
over 30 days - red

I need to be able to have each color total up into three different cells,
meaning, 3 green, 10 yellow, 31 red. Is there a formula to do that
automatically?

Thank you for any assistance.
 
P

Peo Sjoblom

Use the same conditions like you used for your formatting

for instance if A2:A10 are the dates with cf that you compare with today you
would use something like this

=SUMPRODUCT(--(TODAY()>=A2:A10),--(TODAY()<=A2:A10+14))


for the 1-14 days


if you have the compare date in a cell replace TODAY() with that cell


do similar for the other conditions

--


Regards,


Peo Sjoblom
 
P

Pete_UK

Basically, you use the formulae that are used to set up your
conditions for the CF. So, you could have something like this,
assuming the colours are in column A:

for green: =COUNTIF(A:A,"<=14")

for yellow: =COUNTIF(A:A,"<=30") - COUNTIF(A:A,"<=14")

for red: =COUNTIF(A:A,">30")

Hope this helps.

Pete
 
A

Andy Smith

Assuming the column is Column A, you want the following three formulas:

Green (1-14): =SUMIF($A:$A,"<15")
Yellow (15-20): =SUMIF($A:$A,">=15")-{Red formula cell}
Red (30+): =SUMIF($A:$A,">30")

Re the Yellow formula: SUMIF accepts only one condition, so that formula
adds up everything 15 days or older, but then subtracts everything older than
30 days by referring to the cell with the Red formula.
 
F

FlorencePS

To the three of you who answered, thank you. I failed to give some important
info, however, and even wonder if this is now possible.

(I did try the various formulas that each of you supplied, and that is where
I discovered my error.)

The #s that I need to count in col. H are only if they're Open docs (listed
in col. G) so I need to weed those #s out from the Closed or N/A docs in G.

Does that make sense? And is that possible?
 
P

Peo Sjoblom

You could use

=SUMPRODUCT(--(TODAY()>=A2:A10),--(TODAY()<=A2:A10+14),--(G2:G10="Open"))

replace A with H or whatever column you are testing for days since a date

--


Regards,


Peo Sjoblom
 
F

FlorencePS

ok, I tried this and pasted:
=SUMPRODUCT(--(TODAY()>=H:H),--(TODAY()<=H:H+14),--(G:G="Open")) and it's
giving me a #NUM! error. What am I doing wrong?
 
P

Pete_UK

You can't use full-column references with SUMPRODUCT in Excel 2003 and
earlier. Change H:H and G;G to ranges which cover your data.

Hope this helps.

Pete
 
F

FlorencePS

Wasn't sure what you meant by your suggestion, Pete, but I tried:
=SUMPRODUCT(--(TODAY()>=H8:H1508),--(TODAY()<=H8:H1508+14),--(G8:G1508="Open"))
and still got a #Value! error. I know I'm doing something wrong, but have no
idea what.
 
P

Pete_UK

Try it this way:

=SUMPRODUCT(--(TODAY()>=H8:H1508),--(TODAY()-14<=H8:H1508),--
(G8:G1508="Ope­n"))

If you still get an error message then perhaps your "dates" in column
H are really text values that happen to look like dates, so you may
need to do this:

=SUMPRODUCT(--(TODAY()>=--H8:H1508),--(TODAY()-14<=--H8:H1508),--
(G8:G1508="Ope­n"))

assuming that they are in a recognisable date format.

Hope this helps.

Pete
 
F

FlorencePS

Sorry, Pete. I tried both of your suggestions, but to no avail; still
getting the error message

I checked, and the date formatting is there, however, would it make a
difference if any of the cells are either blank or marked N/A in column H?

PS: I'm not not familiar with the double dashes in a formula. Can you
explain what that tells the formula to do?
 

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