Search between dates & return other column

[sonic-the-mouse]

I am looking to solve the following problem

Col A Col B Col C Col D
Date received Date Processed Days taken Are days greater than 3
1/2/3 2/2/3 =b2-a2 if(c2>3,1,0)
1/2/3 5/2/3 4 1
1/2/3 2/2/3 1 0
2/2/3 5/2/3 3 0
5/2/3 9/2/3 4 1
6/2/3 7/2/3 1 0
etc

Cell E1 is a start date, Cell E2 is a stop date. Problem is what
formula would I put in cell E3 to count the number of incident between
the start and the stop date where Col D for the relevant entries are
1?

Any help is appreciated.

s-t-m

 

[T. Valko]

So what date column should be checked against the start and stop dates in E1
and E2? The Date Received or the Date Processed? Or both?

Need a better explanation.

 

[sonic-the-mouse]

Sorry for omitting that part, I would be calculating the dates in Column
A, so if the start date was 1/2/3 and the end date was 2/2/3 it would
return the answer 1 (as there is only 1 incident between these dates
which took longer than 3 days to process. If the start date was 1/2/3
and the end date was 6/2/3 then it would return 2. Hope this helps.

s-t-m

 

[T. Valko]

Try this:

=SUMPRODUCT(--(A2:A10>=E1),--(A2:A10<=E2),--(D210=1))

 

[sonic-the-mouse]

Tried formula below however it just returns zero as an answer no matter
what dates are put in. Any other advice

Try this:

=SUMPRODUCT(--(A2:A10=E1),--(A2:A10=E2),--(D210=1))

--
Biff
Microsoft Excel MVP


"sonic-the-mouse" (e-mail address removed) wrote
in
message

 

[T. Valko]

That usually means your dates aren't true Excel dates or the dates you
entered in E1:E2 aren't true Excel dates.

In Excel a date is just a number formatted to look like a date. So, if you
enter a true Excel date in a cell:

A1 = 1/1/2009

You can test the date to see if it is in fact a true Excel date:

=ISNUMBER(A1)

Will return TRUE if the entry is a true Excel date. If it retunrs FALSE then
it's not a true Excel date and you need to fix the problem.

--
Biff
Microsoft Excel MVP

Tried formula below however it just returns zero as an answer no matter
what dates are put in. Any other advice

Try this:

=SUMPRODUCT(--(A2:A10=E1),--(A2:A10=E2),--(D210=1))

--
Biff
Microsoft Excel MVP


"sonic-the-mouse" (e-mail address removed) wrote
in
message

 

[sonic-the-mouse]

Checked all dates and they are true dates in excel, however SUMPRODUCT
is still returning zeros.

In F1 I am using

=COUNTIF(a2:a10,">="&E1)-COUNTIF(a2:a10,">"&e2)

to return the number of incidents between the dates, but still cannot
work out how to find out how many of those incidents have taken longer
than 3 days to process. Any advice?

s-t-m

 

[sonic-the-mouse]

Been playing about with the SUMPRODUCT formula and input

=SUMPRODUCT(--(A2:A10>=E1),--(A2:A10<=E2),--(D210=1))

The <> signs appear to be returning the correct answer! So I fully
understand the formula can you explain what the "--" signifies,
please?

s-t-m

 

[David Biddulph]

http://xldynamic.com/source/xld.SUMPRODUCT.html
http://www.mcgimpsey.com/excel/formulae/doubleneg.html