count an entry if ????

[Jerry W]

I know this has probably been addressed but cant locate my specific issue:
I would like to count entries in columns b & c that meet criteria in column
a. Here is an example:
Date Cash Check
01/19/05 2.00
01/19/05 5.00
01/19/05 5.00
01/19/05 3.00
02/28/05 1.00
02/28/05 2.00

What formula would give these counts as the end result?:
01/19/05 2 2
02/28/05 1 1
Thanks - Jerry

[Bob Phillips]

=SUMPRODUCT(--($A$1:$A$100=--"2005-01-19"),--($B$1:$B$100>0))

and

=SUMPRODUCT(--($A$1:$A$100=--"2005-01-19"),--($C$1:$C$100>0))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jerry W" <[email protected]> wrote in message
news:[email protected]...
> I know this has probably been addressed but cant locate my specific issue:
> I would like to count entries in columns b & c that meet criteria in
column
> a. Here is an example:
> Date Cash Check
> 01/19/05 2.00
> 01/19/05 5.00
> 01/19/05 5.00
> 01/19/05 3.00
> 02/28/05 1.00
> 02/28/05 2.00
>
> What formula would give these counts as the end result?:
> 01/19/05 2 2
> 02/28/05 1 1
> Thanks - Jerry

[Aladin Akyurek]

=SUMPRODUCT(--(DateRange=Date),--ISNUMBER(CashRange))

=SUMPRODUCT(--(DateRange=Date),--ISNUMBER(CheckRange))

Ranges cannot refer to whole columns like A:A in this type of formula.

Jerry W wrote:
> I know this has probably been addressed but cant locate my specific issue:
> I would like to count entries in columns b & c that meet criteria in column
> a. Here is an example:
> Date Cash Check
> 01/19/05 2.00
> 01/19/05 5.00
> 01/19/05 5.00
> 01/19/05 3.00
> 02/28/05 1.00
> 02/28/05 2.00
>
> What formula would give these counts as the end result?:
> 01/19/05 2 2
> 02/28/05 1 1
> Thanks - Jerry

[Jerry W]

Bob - Thanks for the quick reply. Sorry, I did not mention the entries in b
& c are derived from formulas themselves. Your formula is working except it
is counting the formula (I think) within the counted cell so I am coming up
with a count of 4 instead of 2.

"Bob Phillips" wrote:

> =SUMPRODUCT(--($A$1:$A$100=--"2005-01-19"),--($B$1:$B$100>0))
>
> and
>
> =SUMPRODUCT(--($A$1:$A$100=--"2005-01-19"),--($C$1:$C$100>0))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Jerry W" <[email protected]> wrote in message
> news:[email protected]...
> > I know this has probably been addressed but cant locate my specific issue:
> > I would like to count entries in columns b & c that meet criteria in
> column
> > a. Here is an example:
> > Date Cash Check
> > 01/19/05 2.00
> > 01/19/05 5.00
> > 01/19/05 5.00
> > 01/19/05 3.00
> > 02/28/05 1.00
> > 02/28/05 2.00
> >
> > What formula would give these counts as the end result?:
> > 01/19/05 2 2
> > 02/28/05 1 1
> > Thanks - Jerry
>
>
>

[Bob Phillips]

Jerry,

Try this then

=SUMPRODUCT(--($A$1:$A$100=--"2005-01-19"),--(ISNUMBER($B$1:$B$100)))

and

=SUMPRODUCT(--($A$1:$A$100=--"2005-01-19"),--(ISNUMBER($C$1:$C$100)))

as Aladin also suggested.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jerry W" <[email protected]> wrote in message
news:[email protected]...
> Bob - Thanks for the quick reply. Sorry, I did not mention the entries in
b
> & c are derived from formulas themselves. Your formula is working except
it
> is counting the formula (I think) within the counted cell so I am coming
up
> with a count of 4 instead of 2.
>
> "Bob Phillips" wrote:
>
> > =SUMPRODUCT(--($A$1:$A$100=--"2005-01-19"),--($B$1:$B$100>0))
> >
> > and
> >
> > =SUMPRODUCT(--($A$1:$A$100=--"2005-01-19"),--($C$1:$C$100>0))
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Jerry W" <[email protected]> wrote in message
> > news:[email protected]...
> > > I know this has probably been addressed but cant locate my specific
issue:
> > > I would like to count entries in columns b & c that meet criteria in
> > column
> > > a. Here is an example:
> > > Date Cash Check
> > > 01/19/05 2.00
> > > 01/19/05 5.00
> > > 01/19/05 5.00
> > > 01/19/05 3.00
> > > 02/28/05 1.00
> > > 02/28/05 2.00
> > >
> > > What formula would give these counts as the end result?:
> > > 01/19/05 2 2
> > > 02/28/05 1 1
> > > Thanks - Jerry
> >
> >
> >

[Jerry W]

PERFECTOMUNDO!!!
=SUMPRODUCT(--($A$1:$A$100=--"2005-01-19"),--(ISNUMBER($B$1:$B$100))) worked
just fine. Thank you so much...Jerrry W - Safety Harbor, FL

"Bob Phillips" wrote:

> Jerry,
>
> Try this then
>
> =SUMPRODUCT(--($A$1:$A$100=--"2005-01-19"),--(ISNUMBER($B$1:$B$100)))
>
> and
>
> =SUMPRODUCT(--($A$1:$A$100=--"2005-01-19"),--(ISNUMBER($C$1:$C$100)))
>
> as Aladin also suggested.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Jerry W" <[email protected]> wrote in message
> news:[email protected]...
> > Bob - Thanks for the quick reply. Sorry, I did not mention the entries in
> b
> > & c are derived from formulas themselves. Your formula is working except
> it
> > is counting the formula (I think) within the counted cell so I am coming
> up
> > with a count of 4 instead of 2.
> >
> > "Bob Phillips" wrote:
> >
> > > =SUMPRODUCT(--($A$1:$A$100=--"2005-01-19"),--($B$1:$B$100>0))
> > >
> > > and
> > >
> > > =SUMPRODUCT(--($A$1:$A$100=--"2005-01-19"),--($C$1:$C$100>0))
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Jerry W" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I know this has probably been addressed but cant locate my specific
> issue:
> > > > I would like to count entries in columns b & c that meet criteria in
> > > column
> > > > a. Here is an example:
> > > > Date Cash Check
> > > > 01/19/05 2.00
> > > > 01/19/05 5.00
> > > > 01/19/05 5.00
> > > > 01/19/05 3.00
> > > > 02/28/05 1.00
> > > > 02/28/05 2.00
> > > >
> > > > What formula would give these counts as the end result?:
> > > > 01/19/05 2 2
> > > > 02/28/05 1 1
> > > > Thanks - Jerry
> > >
> > >
> > >
>
>
>