Excel 2003 - Adding up cells that contain Conditional Formatting
 

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

Excel 2003 - Adding up cells that contain Conditional Formatting
 

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

Excel 2003 - Adding up cells that contain Conditional Formatting
 

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

Excel 2003 - Adding up cells that contain Conditional Formatting
 

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

Excel 2003 - Adding up cells that contain Conditional Formatting
 

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

Excel 2003 - Adding up cells that contain Conditional Formatting
 

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

Excel 2003 - Adding up cells that contain Conditional Formatti
 

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

Excel 2003 - Adding up cells that contain Conditional Formatti
 

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 -

Excel 2003 - Adding up cells that contain Conditional Formatti
 

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 -

Excel 2003 - Adding up cells that contain Conditional Formatti
 

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

On Sep 18, 3:05*pm, FlorencePS wrote:
Wasn't sure what you meant by your suggestion, Pete, but I tried: *
=SUMPRODUCT(--(TODAY()=H8:H1508),--(TODAY()<=H8:H1508+14),--(G8:G1508="Ope*n"))
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 -- Hide quoted text -

- Show quoted text -

Excel 2003 - Adding up cells that contain Conditional Formatti
 

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

"Pete_UK" wrote:

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

On Sep 18, 3:05 pm, FlorencePS wrote:
Wasn't sure what you meant by your suggestion, Pete, but I tried:
=SUMPRODUCT(--(TODAY()=H8:H1508),--(TODAY()<=H8:H1508+14),--(G8:G1508="OpeÂ*n"))
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 -- Hide quoted text -

- Show quoted text -