Function

B

Brooke

I have the following formula -

=SUM(IF(FAWAD!$O$2:$O$4000=$E$2,IF(FAWAD!$L$2:$L$4000>=DATEVALUE("4/25/2008"),1,0)))-SUM(IF(FAWAD!$O$2:$O$4000=$E$2,IF(FAWAD!$L$2:$L$4000>DATEVALUE("5/1/2008"),1,0)))

I want to include another datevalue range and I'm not sure how to
incorporate it into this formula. Like if Cells O = Yes (E2) and Cells L =
Certain Date Range and then I want to add another If Cells P = another
certain date range. It will then count total number of accounts.

Criterial in the end being

1. Has to equal Yes
2. Contract date needs to equal specific Date Range
3. Visited date needs to equal specific Date Range

Then it counts total number of accounts with the above criteria.

Is it possible to add to date ranges to the formula??

Thanks
Brooke
 
P

PCLIVE

Try this:

=SUMPRODUCT(--(FAWAD!$O$2:$O$4000=$E$2),--(FAWAD!$L$2:$L$4000>=DATEVALUE("4/25/2008")),--(FAWAD!$L$2:$L$4000<=DATEVALUE("5/1/2008")))

HTH,
Paul
 
B

Brooke

That gives me the first date range, but I also want to include another column
like K with the same datevalue range. So both columns have to have that
date range before it will count.

Can I just do another
(--(FAWAD!$L$2:$L$4000>=DATEVALUE("4/25/2008")),--(FAWAD!$L$2:$L$4000<=DATEVALUE("5/1/2008"))) ?????
 
P

PCLIVE

If I understand you correctly then yes.

Heres the breakdown. The formula below will count rows that include all of
the following criteria.

-FAWAD!$O$2:$O$4000 These rows have to equal what is in $E$2
-FAWAD!$L$2:$L$4000 These rows have to be greater than or equal to
4/25/2008 AND...
They have to be less than or
equal to 5/1/2008
-FAWAD!$K$2:$K$4000 These rows have to be greater than or equal to
4/25/2008 AND...
They have to be less than or
equal to 5/1/2008

All of this criteria must be met in order to be counted.


=SUMPRODUCT(--(FAWAD!$O$2:$O$4000=$E$2),--(FAWAD!$L$2:$L$4000>=DATEVALUE("4/25/2008")),--(FAWAD!$L$2:$L$4000<=DATEVALUE("5/1/2008")),--(FAWAD!$K$2:$K$4000>=DATEVALUE("4/25/2008")),--(FAWAD!$K$2:$K$4000<=DATEVALUE("5/1/2008")))

Regards,
Paul

--
 
B

Brooke

Perfect. Thank you so much.

PCLIVE said:
If I understand you correctly then yes.

Heres the breakdown. The formula below will count rows that include all of
the following criteria.

-FAWAD!$O$2:$O$4000 These rows have to equal what is in $E$2
-FAWAD!$L$2:$L$4000 These rows have to be greater than or equal to
4/25/2008 AND...
They have to be less than or
equal to 5/1/2008
-FAWAD!$K$2:$K$4000 These rows have to be greater than or equal to
4/25/2008 AND...
They have to be less than or
equal to 5/1/2008

All of this criteria must be met in order to be counted.


=SUMPRODUCT(--(FAWAD!$O$2:$O$4000=$E$2),--(FAWAD!$L$2:$L$4000>=DATEVALUE("4/25/2008")),--(FAWAD!$L$2:$L$4000<=DATEVALUE("5/1/2008")),--(FAWAD!$K$2:$K$4000>=DATEVALUE("4/25/2008")),--(FAWAD!$K$2:$K$4000<=DATEVALUE("5/1/2008")))

Regards,
Paul
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top