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

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


"FlorencePS" wrote in message
...
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.
--
FlorencePS

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



On Sep 14, 9:33*pm, FlorencePS wrote:
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.
--
FlorencePS

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.


--
* Please click Yes if this was helpful *
Andy Smith
Senior Systems Analyst
Standard & Poor''s, NYC



"FlorencePS" wrote:

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.
--
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?
--
FlorencePS
Intermediate to Advanced User


"FlorencePS" wrote:

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

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" wrote in message
...
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?
--
FlorencePS
Intermediate to Advanced User


"FlorencePS" wrote:

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.
--
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?
--
FlorencePS
Intermediate to Advanced User

"Peo Sjoblom" wrote:

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" wrote in message
...
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?
--
FlorencePS
Intermediate to Advanced User


"FlorencePS" wrote:

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

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

On Sep 18, 2:33*pm, FlorencePS wrote:
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?
--
FlorencePS
Intermediate to Advanced User



"Peo Sjoblom" wrote:
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" wrote in message
...
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?
--
FlorencePS
Intermediate to Advanced User

"FlorencePS" wrote:

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.
--
FlorencePS- Hide quoted text -

- Show quoted text -

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


"Pete_UK" wrote:

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

On Sep 18, 2:33 pm, FlorencePS wrote:
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?
--
FlorencePS

"Peo Sjoblom" wrote:
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" wrote in message
...
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?
--
FlorencePS
Intermediate to Advanced User

"FlorencePS" wrote:

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.
--
FlorencePS- Hide quoted text -

- Show quoted text -