Looking in one col, counting in another. OFFSET?INDEX? Help!!

[Justin]

I have used this site often with great success, and now I finally have a
question!

I have a column of time stamps and I want to go down to a certain date and
then look to a column on the left and see how many cells have the word "Open"
in them. There can be any number of rows of each date, from none to
(realistically) say, 10. I only want to know how many cells say "Open" for
that date.

I want to use this by having the user just input a certain date, and then
the results of the "Open" status would be displayed for that date. I
actually want to know the total number of the same dates, and the number of
Open's, and get the percentage of Opens to the total.

For example, I want to input date of "4/29/06" and the result for "Open
would be 1. For 4/30 it would be 2, and 5/1 would equal 2.

Column…..

C H

Closed 04/29/2006 21:12
Open 04/29/2006 23:59
Done 04/30/2006 05:50
Open 04/30/2006 22:10
Open 04/30/2006 23:30
Open 05/01/2006 18:05
Open 05/01/2006 19:05
Done 05/01/2006 20:05


Thanks,
Justin

[Bondi]

Hi Justin,

Assuming your data is in column A and B you can use

=SUMPRODUCT((A1:A8="Open")*(B1:B8=C1))/COUNTIF(B1:B8,C1)

To get the pct of Open on a date entered in C1

Regards,
Bondi

[Bob Phillips]

=SUMPRODUCT(--($B$1:$B$100=--"2006-04-30"),--($A$1:$A$100="Open"))

You could put the date and Open in another cell and test that

=SUMPRODUCT(--($B$1:$B$100=K1),--($A$1:$A$100=K2))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Justin" <[email protected]> wrote in message
news:[email protected]...
> I have used this site often with great success, and now I finally have a
> question!
>
> I have a column of time stamps and I want to go down to a certain date and
> then look to a column on the left and see how many cells have the word
"Open"
> in them. There can be any number of rows of each date, from none to
> (realistically) say, 10. I only want to know how many cells say "Open"
for
> that date.
>
> I want to use this by having the user just input a certain date, and then
> the results of the "Open" status would be displayed for that date. I
> actually want to know the total number of the same dates, and the number
of
> Open's, and get the percentage of Opens to the total.
>
> For example, I want to input date of "4/29/06" and the result for "Open
> would be 1. For 4/30 it would be 2, and 5/1 would equal 2.
>
> Column...
>
> C H
>
> Closed 04/29/2006 21:12
> Open 04/29/2006 23:59
> Done 04/30/2006 05:50
> Open 04/30/2006 22:10
> Open 04/30/2006 23:30
> Open 05/01/2006 18:05
> Open 05/01/2006 19:05
> Done 05/01/2006 20:05
>
>
> Thanks,
> Justin
>
>

[Justin]

Thanks for the responses, but I still have a bit of a problem!

I was able to get it working, and to use only the date part of the timestamp
using the LEFT function:

=SUMPRODUCT((A1:A100="Open")*(LEFT(B1:B100,10)="04/30/2006"))

Or

=SUMPRODUCT(--((LEFT($A$1:$A$100,10))="04/30/2006"),--($B1$:$B$100="Open"))

But it doesn’t work when I replace the date in quotes in the formula with a
cell that contains the input date, it always comes back with a "0". I want
the user to be able to simply input a date and then this will extract the
count of "Open". I suspect this has to do with the formatting, and the
timestamp dates are test (I think). I don’t want to make the user input
the date desired in text format if I can help it.


"Bob Phillips" wrote:

> =SUMPRODUCT(--($B$1:$B$100=--"2006-04-30"),--($A$1:$A$100="Open"))
>
> You could put the date and Open in another cell and test that
>
> =SUMPRODUCT(--($B$1:$B$100=K1),--($A$1:$A$100=K2))
>
> --
> HTH
>
> Bob Phillips
>
> (remove xxx from email address if mailing direct)
>
> "Justin" <[email protected]> wrote in message
> news:[email protected]...
> > I have used this site often with great success, and now I finally have a
> > question!
> >
> > I have a column of time stamps and I want to go down to a certain date and
> > then look to a column on the left and see how many cells have the word
> "Open"
> > in them. There can be any number of rows of each date, from none to
> > (realistically) say, 10. I only want to know how many cells say "Open"
> for
> > that date.
> >
> > I want to use this by having the user just input a certain date, and then
> > the results of the "Open" status would be displayed for that date. I
> > actually want to know the total number of the same dates, and the number
> of
> > Open's, and get the percentage of Opens to the total.
> >
> > For example, I want to input date of "4/29/06" and the result for "Open
> > would be 1. For 4/30 it would be 2, and 5/1 would equal 2.
> >
> > Column...
> >
> > C H
> >
> > Closed 04/29/2006 21:12
> > Open 04/29/2006 23:59
> > Done 04/30/2006 05:50
> > Open 04/30/2006 22:10
> > Open 04/30/2006 23:30
> > Open 05/01/2006 18:05
> > Open 05/01/2006 19:05
> > Done 05/01/2006 20:05
> >
> >
> > Thanks,
> > Justin
> >
> >
>
>
>

[Bob Phillips]

That sounds as if your date cell is text, not a true date.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Justin" <[email protected]> wrote in message
news:[email protected]...
> Thanks for the responses, but I still have a bit of a problem!
>
> I was able to get it working, and to use only the date part of the
timestamp
> using the LEFT function:
>
> =SUMPRODUCT((A1:A100="Open")*(LEFT(B1:B100,10)="04/30/2006"))
>
> Or
>
>
=SUMPRODUCT(--((LEFT($A$1:$A$100,10))="04/30/2006"),--($B1$:$B$100="Open"))
>
> But it doesn't work when I replace the date in quotes in the formula with
a
> cell that contains the input date, it always comes back with a "0". I
want
> the user to be able to simply input a date and then this will extract the
> count of "Open". I suspect this has to do with the formatting, and the
> timestamp dates are test (I think). I don't want to make the user input
> the date desired in text format if I can help it.
>
>
> "Bob Phillips" wrote:
>
> > =SUMPRODUCT(--($B$1:$B$100=--"2006-04-30"),--($A$1:$A$100="Open"))
> >
> > You could put the date and Open in another cell and test that
> >
> > =SUMPRODUCT(--($B$1:$B$100=K1),--($A$1:$A$100=K2))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove xxx from email address if mailing direct)
> >
> > "Justin" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have used this site often with great success, and now I finally have
a
> > > question!
> > >
> > > I have a column of time stamps and I want to go down to a certain date
and
> > > then look to a column on the left and see how many cells have the word
> > "Open"
> > > in them. There can be any number of rows of each date, from none to
> > > (realistically) say, 10. I only want to know how many cells say
"Open"
> > for
> > > that date.
> > >
> > > I want to use this by having the user just input a certain date, and
then
> > > the results of the "Open" status would be displayed for that date. I
> > > actually want to know the total number of the same dates, and the
number
> > of
> > > Open's, and get the percentage of Opens to the total.
> > >
> > > For example, I want to input date of "4/29/06" and the result for
"Open
> > > would be 1. For 4/30 it would be 2, and 5/1 would equal 2.
> > >
> > > Column...
> > >
> > > C H
> > >
> > > Closed 04/29/2006 21:12
> > > Open 04/29/2006 23:59
> > > Done 04/30/2006 05:50
> > > Open 04/30/2006 22:10
> > > Open 04/30/2006 23:30
> > > Open 05/01/2006 18:05
> > > Open 05/01/2006 19:05
> > > Done 05/01/2006 20:05
> > >
> > >
> > > Thanks,
> > > Justin
> > >
> > >
> >
> >
> >

[Justin]

Thanks for the answers, I was able to get it working using the the TEXT
function since my imported dates are in text format. The TEXT function
converts the user input date to text, then uses that for comparison in the
columns.

=SUMPRODUCT(--((LEFT($A$27:$A$100,10))=TEXT(J10,"mm/dd/yyyy")),--($C$27:$C$100="Open"))

With column A with the text strings like: "04/29/2006 21:12" , and J10 has
the user input date, in date format, and column C has the choices including
"Open".

Thanks again!
Justin

"Justin" wrote:

> I have used this site often with great success, and now I finally have a
> question!
>
> I have a column of time stamps and I want to go down to a certain date and
> then look to a column on the left and see how many cells have the word "Open"
> in them. There can be any number of rows of each date, from none to
> (realistically) say, 10. I only want to know how many cells say "Open" for
> that date.
>
> I want to use this by having the user just input a certain date, and then
> the results of the "Open" status would be displayed for that date. I
> actually want to know the total number of the same dates, and the number of
> Open's, and get the percentage of Opens to the total.
>
> For example, I want to input date of "4/29/06" and the result for "Open
> would be 1. For 4/30 it would be 2, and 5/1 would equal 2.
>
> Column…..
>
> C H
>
> Closed 04/29/2006 21:12
> Open 04/29/2006 23:59
> Done 04/30/2006 05:50
> Open 04/30/2006 22:10
> Open 04/30/2006 23:30
> Open 05/01/2006 18:05
> Open 05/01/2006 19:05
> Done 05/01/2006 20:05
>
>
> Thanks,
> Justin
>
>

[Bob Phillips]

Does this also work?

=SUMPRODUCT(--(NOT(ISERROR($A$27:$A$100)=J10)),--($C$27:$C$100="Open"))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Justin" <[email protected]> wrote in message
news:[email protected]...
> Thanks for the answers, I was able to get it working using the the TEXT
> function since my imported dates are in text format. The TEXT function
> converts the user input date to text, then uses that for comparison in the
> columns.
>
>
=SUMPRODUCT(--((LEFT($A$27:$A$100,10))=TEXT(J10,"mm/dd/yyyy")),--($C$27:$C$1
00="Open"))
>
> With column A with the text strings like: "04/29/2006 21:12" , and J10 has
> the user input date, in date format, and column C has the choices
including
> "Open".
>
> Thanks again!
> Justin
>
> "Justin" wrote:
>
> > I have used this site often with great success, and now I finally have a
> > question!
> >
> > I have a column of time stamps and I want to go down to a certain date
and
> > then look to a column on the left and see how many cells have the word
"Open"
> > in them. There can be any number of rows of each date, from none to
> > (realistically) say, 10. I only want to know how many cells say "Open"
for
> > that date.
> >
> > I want to use this by having the user just input a certain date, and
then
> > the results of the "Open" status would be displayed for that date. I
> > actually want to know the total number of the same dates, and the number
of
> > Open's, and get the percentage of Opens to the total.
> >
> > For example, I want to input date of "4/29/06" and the result for "Open
> > would be 1. For 4/30 it would be 2, and 5/1 would equal 2.
> >
> > Column...
> >
> > C H
> >
> > Closed 04/29/2006 21:12
> > Open 04/29/2006 23:59
> > Done 04/30/2006 05:50
> > Open 04/30/2006 22:10
> > Open 04/30/2006 23:30
> > Open 05/01/2006 18:05
> > Open 05/01/2006 19:05
> > Done 05/01/2006 20:05
> >
> >
> > Thanks,
> > Justin
> >
> >