countif using multiple criteria

I am trying to calculate how many times an event happens with multiple
criteria. As i understand it COUNTIF will not work. But SUMPRODUCT is the way
to go. I have tried this but am unable to get to work.

I am trying to find out how many time "new" occurs in a given month say from
1st Jan to 31st Jan so my formula read
=sumproduct((J20:J100>=01/01/06),(j20:j100<=31/01/06),(g20:G100="new")
What am I doing wrong?

=SUMPRODUCT(--(J20:J100>=--"1/1/6"),--(J20:J100<=--"31/1/6"),--(G20:G100="new"))

however a better way would be

=SUMPRODUCT(--(J20:J100>=DATE(2006,1,1)),--(J20:J100<=DATE(2006,1,31)),--(G20:G100="new"))

since it is not region centric regarding the date format

--
Regards,

Peo Sjoblom

Portland, Oregon




"Wayne E" <Wayne E@discussions.microsoft.com> wrote in message
news:9B2445F4-D6DD-4386-AEFD-48915453BE39@microsoft.com...
>I am trying to calculate how many times an event happens with multiple
> criteria. As i understand it COUNTIF will not work. But SUMPRODUCT is the
> way
> to go. I have tried this but am unable to get to work.
>
> I am trying to find out how many time "new" occurs in a given month say
> from
> 1st Jan to 31st Jan so my formula read
> =sumproduct((J20:J100>=01/01/06),(j20:j100<=31/01/06),(g20:G100="new")
> What am I doing wrong?
>
>
>

Thanks Peo
I tried your suggestion but it is still not giving me the correct result.

"Peo Sjoblom" wrote:

> =SUMPRODUCT(--(J20:J100>=--"1/1/6"),--(J20:J100<=--"31/1/6"),--(G20:G100="new"))
>
> however a better way would be
>
> =SUMPRODUCT(--(J20:J100>=DATE(2006,1,1)),--(J20:J100<=DATE(2006,1,31)),--(G20:G100="new"))
>
> since it is not region centric regarding the date format
>
> --
> Regards,
>
> Peo Sjoblom
>
> Portland, Oregon
>
>
>
>
> "Wayne E" <Wayne E@discussions.microsoft.com> wrote in message
> news:9B2445F4-D6DD-4386-AEFD-48915453BE39@microsoft.com...
> >I am trying to calculate how many times an event happens with multiple
> > criteria. As i understand it COUNTIF will not work. But SUMPRODUCT is the
> > way
> > to go. I have tried this but am unable to get to work.
> >
> > I am trying to find out how many time "new" occurs in a given month say
> > from
> > 1st Jan to 31st Jan so my formula read
> > =sumproduct((J20:J100>=01/01/06),(j20:j100<=31/01/06),(g20:G100="new")
> > What am I doing wrong?
> >
> >
> >
>
>

I think it's giving the correct solution--but maybe your data isn't what you
expected it to be. (And your range is correct, right?)

Another option that will return the same value as Peo's formula:

=sumproduct(--(text(j20:J100,"yyyymm")="200601"),--(g20:g100="new"))

=====
You could apply data|filter|autofilter to that range. Filter to show just the
january dates and then filter to show the "new" rows.

I think you'll see that all the formulas give the same result as what you see.

======
If all this doesn't work, post the formula you're using.

Wayne E wrote:
>
> Thanks Peo
> I tried your suggestion but it is still not giving me the correct result.
>
> "Peo Sjoblom" wrote:
>
> > =SUMPRODUCT(--(J20:J100>=--"1/1/6"),--(J20:J100<=--"31/1/6"),--(G20:G100="new"))
> >
> > however a better way would be
> >
> > =SUMPRODUCT(--(J20:J100>=DATE(2006,1,1)),--(J20:J100<=DATE(2006,1,31)),--(G20:G100="new"))
> >
> > since it is not region centric regarding the date format
> >
> > --
> > Regards,
> >
> > Peo Sjoblom
> >
> > Portland, Oregon
> >
> >
> >
> >
> > "Wayne E" <Wayne E@discussions.microsoft.com> wrote in message
> > news:9B2445F4-D6DD-4386-AEFD-48915453BE39@microsoft.com...
> > >I am trying to calculate how many times an event happens with multiple
> > > criteria. As i understand it COUNTIF will not work. But SUMPRODUCT is the
> > > way
> > > to go. I have tried this but am unable to get to work.
> > >
> > > I am trying to find out how many time "new" occurs in a given month say
> > > from
> > > 1st Jan to 31st Jan so my formula read
> > > =sumproduct((J20:J100>=01/01/06),(j20:j100<=31/01/06),(g20:G100="new")
> > > What am I doing wrong?
> > >
> > >
> > >
> >
> >

--

Dave Peterson

Hi!

Try this:

A1 = 1/1/2006
B1 = 1/31/2006
C1 = new

=SUMPRODUCT(--(J20:J100>=A1),--(J20:J100<=B1),--(G20:G100=C1))

Biff

"Wayne E" <Wayne E@discussions.microsoft.com> wrote in message
news:9B2445F4-D6DD-4386-AEFD-48915453BE39@microsoft.com...
>I am trying to calculate how many times an event happens with multiple
> criteria. As i understand it COUNTIF will not work. But SUMPRODUCT is the
> way
> to go. I have tried this but am unable to get to work.
>
> I am trying to find out how many time "new" occurs in a given month say
> from
> 1st Jan to 31st Jan so my formula read
> =sumproduct((J20:J100>=01/01/06),(j20:j100<=31/01/06),(g20:G100="new")
> What am I doing wrong?
>
>
>