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

  • Thread starter Thread starter Lauren
  • Start date Start date
L

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
 
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
 
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!
 
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
 
Back
Top