IF, AND, THEN Conditional Forumla HELP!!!!
 

I need help with a complicated conditional formula. Here is what I want
written out. I do not know how to translate that into an Excel formula. The
data is copied below.

IF column E "Phase" is equal to 1, AND there is data (an effective date less
than today's date) in column D "Effective Date" THEN I want Excel to COUNT
that.

I will also need to count Phases 2 and 3 as well, but let's just start with
Phase 1!!

I hope you can help me!!

I am using Excel 2003

Here is a portion of the data:

Effective Date Phase
1-Jan-07 1
6-Aug-08 2
1-Jun-07 3
1-Jun-07 3
17-Aug-07 2
1-Jun-07 2
15-Jun-07 2
1-Jun-07 2
1-Sep-06 1

IF, AND, THEN Conditional Forumla HELP!!!!
 

Let's go the whole hog !! Put 1, 2, 3 for the Phases in cells M1:M3
(say), and in N1 put this formula:

=SUMPRODUCT((E$1:E$100=M1)*(D$1:D$100<TODAY())*(D$ 1:D$100<""))

I've assumed you have 100 rows of data, so increase this if you have
more. This will give you a count of the number of rows for phase 1
with an effective date less than today. Copy the formula into N2 and
N3 to get a count for phase 2 and phase 3 respectively.

Hope this helps.

Pete

On Apr 10, 4:06*pm, Lauren wrote:
I need help with a complicated conditional formula. Here is what I want
written out. I do not know how to translate that into an Excel formula. The
data is copied below.

IF column E "Phase" is equal to 1, AND there is data (an effective date less
than today's date) in column D "Effective Date" THEN I want Excel to COUNT
that.

I will also need to count Phases 2 and 3 as well, but let's just start with
Phase 1!!

I hope you can help me!!

I am using Excel 2003

Here is a portion of the data:

Effective Date *Phase
1-Jan-07 * * * * * * * * * * * *1
6-Aug-08 * * * * * * * * * * * *2
1-Jun-07 * * * * * * * * * * * *3
1-Jun-07 * * * * * * * * * * * *3
17-Aug-07 * * * * * * * * * * * 2
1-Jun-07 * * * * * * * * * * * *2
15-Jun-07 * * * * * * * * * * * 2
1-Jun-07 * * * * * * * * * * * *2
1-Sep-06 * * * * * * * * * * * *1

IF, AND, THEN Conditional Forumla HELP!!!!
 

Pete,

Thanks for the suggestion. I found a solution and took a different approach,
as with Excel you can do it several ways. I did however attempt your way, and
it also worked. Thanks for the Post!!

Here's what I did:
I created 3 columns to the right of the phase with this formula
=IF(AND(D3<NOW(),E3=1),1,0), changing the E3= to read 2, and 3 respectivley.
I then made a SUM at the bottom of the 500+ records......ugh....

I like your way much better. Is there an easy way to hide the individual
cells with the 1,2,3 without hiding the column or row?

Thanks again!


"Pete_UK" wrote:

Let's go the whole hog !! Put 1, 2, 3 for the Phases in cells M1:M3
(say), and in N1 put this formula:

=SUMPRODUCT((E$1:E$100=M1)*(D$1:D$100<TODAY())*(D$ 1:D$100<""))

I've assumed you have 100 rows of data, so increase this if you have
more. This will give you a count of the number of rows for phase 1
with an effective date less than today. Copy the formula into N2 and
N3 to get a count for phase 2 and phase 3 respectively.

Hope this helps.

Pete

On Apr 10, 4:06 pm, Lauren wrote:
I need help with a complicated conditional formula. Here is what I want
written out. I do not know how to translate that into an Excel formula. The
data is copied below.

IF column E "Phase" is equal to 1, AND there is data (an effective date less
than today's date) in column D "Effective Date" THEN I want Excel to COUNT
that.

I will also need to count Phases 2 and 3 as well, but let's just start with
Phase 1!!

I hope you can help me!!

I am using Excel 2003

Here is a portion of the data:

Effective Date Phase
1-Jan-07 1
6-Aug-08 2
1-Jun-07 3
1-Jun-07 3
17-Aug-07 2
1-Jun-07 2
15-Jun-07 2
1-Jun-07 2
1-Sep-06 1

IF, AND, THEN Conditional Forumla HELP!!!!
 

Hi Lauren,

Glad to help, and the answer to your question is Yes.

You just want a function which can return 1, 2, 3 etc as you copy the
formula down, and such a function is available in ROW(A1) - this returns 1
and when it is copied down it becomes ROW(A2) (returning 2), then ROW(A3)
(returning 3) etc. (The cell reference doesn't mean anything - it's just a
convenient way of getting an incrementing number).

So, you can dispense with the values in M1:M3 and change the formula in N1
to this:

=SUMPRODUCT((E$1:E$600=ROW(A1))*(D$1:D$600<TODAY() )*(D$1:D$600<""))

I've also changed the ranges to suit up to 600 rows, so just copy this down
for as many phases as you have.

Mind you, I would prefer to see the numbers to remind me what the totals
relate to.

Hope this helps.

Pete

"Lauren" wrote in message
...
Pete,

Thanks for the suggestion. I found a solution and took a different
approach,
as with Excel you can do it several ways. I did however attempt your way,
and
it also worked. Thanks for the Post!!

Here's what I did:
I created 3 columns to the right of the phase with this formula
=IF(AND(D3<NOW(),E3=1),1,0), changing the E3= to read 2, and 3
respectivley.
I then made a SUM at the bottom of the 500+ records......ugh....

I like your way much better. Is there an easy way to hide the individual
cells with the 1,2,3 without hiding the column or row?

Thanks again!


"Pete_UK" wrote:

Let's go the whole hog !! Put 1, 2, 3 for the Phases in cells M1:M3
(say), and in N1 put this formula:

=SUMPRODUCT((E$1:E$100=M1)*(D$1:D$100<TODAY())*(D$ 1:D$100<""))

I've assumed you have 100 rows of data, so increase this if you have
more. This will give you a count of the number of rows for phase 1
with an effective date less than today. Copy the formula into N2 and
N3 to get a count for phase 2 and phase 3 respectively.

Hope this helps.

Pete

On Apr 10, 4:06 pm, Lauren wrote:
I need help with a complicated conditional formula. Here is what I want
written out. I do not know how to translate that into an Excel formula.
The
data is copied below.

IF column E "Phase" is equal to 1, AND there is data (an effective date
less
than today's date) in column D "Effective Date" THEN I want Excel to
COUNT
that.

I will also need to count Phases 2 and 3 as well, but let's just start
with
Phase 1!!

I hope you can help me!!

I am using Excel 2003

Here is a portion of the data:

Effective Date Phase
1-Jan-07 1
6-Aug-08 2
1-Jun-07 3
1-Jun-07 3
17-Aug-07 2
1-Jun-07 2
15-Jun-07 2
1-Jun-07 2
1-Sep-06 1

IF, AND, THEN Conditional Forumla HELP!!!!
 

Hi Pete!

Thanks for all the help this was great!!

-Lauren

"Pete_UK" wrote:

Hi Lauren,

Glad to help, and the answer to your question is Yes.

You just want a function which can return 1, 2, 3 etc as you copy the
formula down, and such a function is available in ROW(A1) - this returns 1
and when it is copied down it becomes ROW(A2) (returning 2), then ROW(A3)
(returning 3) etc. (The cell reference doesn't mean anything - it's just a
convenient way of getting an incrementing number).

So, you can dispense with the values in M1:M3 and change the formula in N1
to this:

=SUMPRODUCT((E$1:E$600=ROW(A1))*(D$1:D$600<TODAY() )*(D$1:D$600<""))

I've also changed the ranges to suit up to 600 rows, so just copy this down
for as many phases as you have.

Mind you, I would prefer to see the numbers to remind me what the totals
relate to.

Hope this helps.

Pete

"Lauren" wrote in message
...
Pete,

Thanks for the suggestion. I found a solution and took a different
approach,
as with Excel you can do it several ways. I did however attempt your way,
and
it also worked. Thanks for the Post!!

Here's what I did:
I created 3 columns to the right of the phase with this formula
=IF(AND(D3<NOW(),E3=1),1,0), changing the E3= to read 2, and 3
respectivley.
I then made a SUM at the bottom of the 500+ records......ugh....

I like your way much better. Is there an easy way to hide the individual
cells with the 1,2,3 without hiding the column or row?

Thanks again!


"Pete_UK" wrote:

Let's go the whole hog !! Put 1, 2, 3 for the Phases in cells M1:M3
(say), and in N1 put this formula:

=SUMPRODUCT((E$1:E$100=M1)*(D$1:D$100<TODAY())*(D$ 1:D$100<""))

I've assumed you have 100 rows of data, so increase this if you have
more. This will give you a count of the number of rows for phase 1
with an effective date less than today. Copy the formula into N2 and
N3 to get a count for phase 2 and phase 3 respectively.

Hope this helps.

Pete

On Apr 10, 4:06 pm, Lauren wrote:
I need help with a complicated conditional formula. Here is what I want
written out. I do not know how to translate that into an Excel formula.
The
data is copied below.

IF column E "Phase" is equal to 1, AND there is data (an effective date
less
than today's date) in column D "Effective Date" THEN I want Excel to
COUNT
that.

I will also need to count Phases 2 and 3 as well, but let's just start
with
Phase 1!!

I hope you can help me!!

I am using Excel 2003

Here is a portion of the data:

Effective Date Phase
1-Jan-07 1
6-Aug-08 2
1-Jun-07 3
1-Jun-07 3
17-Aug-07 2
1-Jun-07 2
15-Jun-07 2
1-Jun-07 2
1-Sep-06 1

IF, AND, THEN Conditional Forumla HELP!!!!
 

You're welcome, Lauren - thanks for feeding back.

Pete

"Lauren" wrote in message
...
Hi Pete!

Thanks for all the help this was great!!

-Lauren

"Pete_UK" wrote:

Hi Lauren,

Glad to help, and the answer to your question is Yes.

You just want a function which can return 1, 2, 3 etc as you copy the
formula down, and such a function is available in ROW(A1) - this returns
1
and when it is copied down it becomes ROW(A2) (returning 2), then ROW(A3)
(returning 3) etc. (The cell reference doesn't mean anything - it's just
a
convenient way of getting an incrementing number).

So, you can dispense with the values in M1:M3 and change the formula in
N1
to this:

=SUMPRODUCT((E$1:E$600=ROW(A1))*(D$1:D$600<TODAY() )*(D$1:D$600<""))

I've also changed the ranges to suit up to 600 rows, so just copy this
down
for as many phases as you have.

Mind you, I would prefer to see the numbers to remind me what the totals
relate to.

Hope this helps.

Pete

"Lauren" wrote in message
...
Pete,

Thanks for the suggestion. I found a solution and took a different
approach,
as with Excel you can do it several ways. I did however attempt your
way,
and
it also worked. Thanks for the Post!!

Here's what I did:
I created 3 columns to the right of the phase with this formula
=IF(AND(D3<NOW(),E3=1),1,0), changing the E3= to read 2, and 3
respectivley.
I then made a SUM at the bottom of the 500+ records......ugh....

I like your way much better. Is there an easy way to hide the
individual
cells with the 1,2,3 without hiding the column or row?

Thanks again!


"Pete_UK" wrote:

Let's go the whole hog !! Put 1, 2, 3 for the Phases in cells M1:M3
(say), and in N1 put this formula:

=SUMPRODUCT((E$1:E$100=M1)*(D$1:D$100<TODAY())*(D$ 1:D$100<""))

I've assumed you have 100 rows of data, so increase this if you have
more. This will give you a count of the number of rows for phase 1
with an effective date less than today. Copy the formula into N2 and
N3 to get a count for phase 2 and phase 3 respectively.

Hope this helps.

Pete

On Apr 10, 4:06 pm, Lauren wrote:
I need help with a complicated conditional formula. Here is what I
want
written out. I do not know how to translate that into an Excel
formula.
The
data is copied below.

IF column E "Phase" is equal to 1, AND there is data (an effective
date
less
than today's date) in column D "Effective Date" THEN I want Excel to
COUNT
that.

I will also need to count Phases 2 and 3 as well, but let's just
start
with
Phase 1!!

I hope you can help me!!

I am using Excel 2003

Here is a portion of the data:

Effective Date Phase
1-Jan-07 1
6-Aug-08 2
1-Jun-07 3
1-Jun-07 3
17-Aug-07 2
1-Jun-07 2
15-Jun-07 2
1-Jun-07 2
1-Sep-06 1