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

[Lauren]

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

 

[Pete_UK]

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$100<TODAY())*(D$1$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

 

[Lauren]

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]

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$600<TODAY())*(D$1$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]

Hi Pete!

Thanks for all the help this was great!!

-Lauren

 

[Pete_UK]

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

Pete