Search between dates & return other column
 

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(c23,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




--
sonic-the-mouse

Search between dates & return other column
 

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.

--
Biff
Microsoft Excel MVP


"sonic-the-mouse" wrote in
message ...

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(c23,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




--
sonic-the-mouse

Search between dates & return other column
 

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;3358856 Wrote:
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.

--
Biff
Microsoft Excel MVP


"sonic-the-mouse" wrote
in
message ...-

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(c23,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




--
sonic-the-mouse -




--
sonic-the-mouse

Search between dates & return other column
 

Try this:

=SUMPRODUCT(--(A2:A10=E1),--(A2:A10<=E2),--(D2:D10=1))

--
Biff
Microsoft Excel MVP


"sonic-the-mouse" wrote in
message ...

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;3358856 Wrote:
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.

--
Biff
Microsoft Excel MVP


"sonic-the-mouse" wrote
in
message ...-

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(c23,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




--
sonic-the-mouse -




--
sonic-the-mouse

Search between dates & return other column
 

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

T. Valko;3359458 Wrote: [color=blue][i]
Try this:

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

--
Biff
Microsoft Excel MVP


"sonic-the-mouse" wrote
in
message
...




--
sonic-the-mouse

Search between dates & return other column
 

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


"sonic-the-mouse" wrote in
message ...[color=blue][i]

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

T. Valko;3359458 Wrote:
Try this:

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

--
Biff
Microsoft Excel MVP


"sonic-the-mouse" wrote
in
message
...




--
sonic-the-mouse

Search between dates & return other column
 

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

T. Valko;3360011 Wrote:
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


"sonic-the-mouse" wrote
in
message ...-

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

...--




--
sonic-the-mouse -




--
sonic-the-mouse

Search between dates & return other column
 

Been playing about with the SUMPRODUCT formula and input

=SUMPRODUCT(--(A2:A10=E1),--(A2:A10<=E2),--(D2:D10=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
sonic-the-mouse;3360456 Wrote:
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

Search between dates & return other column
 

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

"sonic-the-mouse" wrote in
message ...

Been playing about with the SUMPRODUCT formula and input

=SUMPRODUCT(--(A2:A10=E1),--(A2:A10<=E2),--(D2:D10=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
sonic-the-mouse;3360456 Wrote:
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