Prev: Protection
Next: Parsing a string
From: Judy Cohen on 24 May 2010 13:35
I want to set up a count of all cells where the date is equal to the date
specified in column A on the sheet and the font for the specified range is
Red.

Currently my formula reads
=SUMPRODUCT(('Master Log'!U$2:U$2600="Red")*('Master Log'!U$2:U$2600=A11))

I need to find out how many cases I processed that were overdue by date so I
can see how many cases I had overdue per month.

The font on the Master Log goes to Red in the conditional formatting if the
item was processed overdue.

Any assistance is greatly appreciated.
From: Tom Hutchins on 24 May 2010 16:59
Replicate the logic tests in your conditional formatting within your
SUMPRODUCT formula. It's otherwise very difficult to identify formatting
applied by conditional formatting.

Hope this helps,

Hutch

"Judy Cohen" wrote:

> I want to set up a count of all cells where the date is equal to the date
> specified in column A on the sheet and the font for the specified range is
> Red.
>
> Currently my formula reads
> =SUMPRODUCT(('Master Log'!U$2:U$2600="Red")*('Master Log'!U$2:U$2600=A11))
>
> I need to find out how many cases I processed that were overdue by date so I
> can see how many cases I had overdue per month.
>
> The font on the Master Log goes to Red in the conditional formatting if the
> item was processed overdue.
>
> Any assistance is greatly appreciated.
From: Judy Cohen on 27 May 2010 16:32
I figured it out. Thank you for the suggestion. I wound up with a simple note
that the date in the one column plus a set of days needed to be earlier then
the date in the target column and the date in the target column was equal to
the date on my summary sheet in that particular target column.

"Judy Cohen" wrote:

> My conditional formatting is on another sheet. For the given column it
> compares dates, if date in the specified column is more than a certain amount
> after the column that I am comparing to it would show as red, do I copy my
> formala from the conditional formatting editor where I have "red" also do I
> enclose the formula in quotes?
>
> "Tom Hutchins" wrote:
>
> > Replicate the logic tests in your conditional formatting within your
> > SUMPRODUCT formula. It's otherwise very difficult to identify formatting
> > applied by conditional formatting.
> >
> > Hope this helps,
> >
> > Hutch
> >
> > "Judy Cohen" wrote:
> >
> > > I want to set up a count of all cells where the date is equal to the date
> > > specified in column A on the sheet and the font for the specified range is
> > > Red.
> > >
> > > Currently my formula reads
> > > =SUMPRODUCT(('Master Log'!U$2:U$2600="Red")*('Master Log'!U$2:U$2600=A11))
> > >
> > > I need to find out how many cases I processed that were overdue by date so I
> > > can see how many cases I had overdue per month.
> > >
> > > The font on the Master Log goes to Red in the conditional formatting if the
> > > item was processed overdue.
> > >
> > > Any assistance is greatly appreciated.
From: Judy Cohen on 27 May 2010 16:33
My conditional formatting is on another sheet. For the given column it
compares dates, if date in the specified column is more than a certain amount
after the column that I am comparing to it would show as red, do I copy my
formala from the conditional formatting editor where I have "red" also do I
enclose the formula in quotes?

"Tom Hutchins" wrote:

> Replicate the logic tests in your conditional formatting within your
> SUMPRODUCT formula. It's otherwise very difficult to identify formatting
> applied by conditional formatting.
>
> Hope this helps,
>
> Hutch
>
> "Judy Cohen" wrote:
>
> > I want to set up a count of all cells where the date is equal to the date
> > specified in column A on the sheet and the font for the specified range is
> > Red.
> >
> > Currently my formula reads
> > =SUMPRODUCT(('Master Log'!U$2:U$2600="Red")*('Master Log'!U$2:U$2600=A11))
> >
> > I need to find out how many cases I processed that were overdue by date so I
> > can see how many cases I had overdue per month.
> >
> > The font on the Master Log goes to Red in the conditional formatting if the
> > item was processed overdue.
> >
> > Any assistance is greatly appreciated.
 | 
Pages: 1
Prev: Protection
Next: Parsing a string