Excel 2003 - Adding up cells that contain Conditional Formatting

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

 

[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

 

[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

 

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

 

[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?

 

[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

 

[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?

 

[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

 

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

 

[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

 

[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?