Formula Problem

=SUMPRODUCT(--(TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501"),--(IndexData!$A$5:$A$23309="AUT"),--(IndexData!$I$5:$I$23309<=3))


--
Regards,

Peo Sjoblom

(No private emails please)


"Jeanette" <Jeanette@discussions.microsoft.com> wrote in message
news:13CB639A-CBC6-478F-A290-802D0A3AF5F0@microsoft.com...
>I know there is a simple solution to my problem. The following is my
>formula:
>
> =COUNT(IF((TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501")*(IndexData!$A$5:$A$23309="AUT"),AND(IndexData!$I$5:$I$23309<=3,""),IndexData!$I$5:$I$23309))
>
> The formula is not bringing back to the number of "AUT" less than or equal
> to 3, if it were the number would be Zero, instead it is coming back with
> 23304 for the month of January when there were no "AUT" completed in less
> than 19 days.
>
> What have I done and how do I fix it? I also need the # of "AUT" between
> 4
> and 5 and Greater than 6.

Thanks ~ I was including "and" so it wasn't working

"Alok" wrote:

> Since you want the AND of the conditions >=4, <=5 all you do is split it into
> two factors
>
> --(indexData!$I$5:$I$23309>=4),--(indexData!$I$5:$I$23309<=5)
>
> Alok
>
> "Jeanette" wrote:
>
> > Thanks ~ now how do I write --(indexData!$I$5:$I$23309>=4<=5) and have it
> > work correctly?
> >
> > "Peo Sjoblom" wrote:
> >
> > > =SUMPRODUCT(--(TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501"),--(IndexData!$A$5:$A$23309="AUT"),--(IndexData!$I$5:$I$23309<=3))
> > >
> > >
> > > --
> > > Regards,
> > >
> > > Peo Sjoblom
> > >
> > > (No private emails please)
> > >
> > >
> > > "Jeanette" <Jeanette@discussions.microsoft.com> wrote in message
> > > news:13CB639A-CBC6-478F-A290-802D0A3AF5F0@microsoft.com...
> > > >I know there is a simple solution to my problem. The following is my
> > > >formula:
> > > >
> > > > =COUNT(IF((TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501")*(IndexData!$A$5:$A$23309="AUT"),AND(IndexData!$I$5:$I$23309<=3,""),IndexData!$I$5:$I$23309))
> > > >
> > > > The formula is not bringing back to the number of "AUT" less than or equal
> > > > to 3, if it were the number would be Zero, instead it is coming back with
> > > > 23304 for the month of January when there were no "AUT" completed in less
> > > > than 19 days.
> > > >
> > > > What have I done and how do I fix it? I also need the # of "AUT" between
> > > > 4
> > > > and 5 and Greater than 6.
> > >
> > >

Since you want the AND of the conditions >=4, <=5 all you do is split it into
two factors

--(indexData!$I$5:$I$23309>=4),--(indexData!$I$5:$I$23309<=5)

Alok

"Jeanette" wrote:

> Thanks ~ now how do I write --(indexData!$I$5:$I$23309>=4<=5) and have it
> work correctly?
>
> "Peo Sjoblom" wrote:
>
> > =SUMPRODUCT(--(TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501"),--(IndexData!$A$5:$A$23309="AUT"),--(IndexData!$I$5:$I$23309<=3))
> >
> >
> > --
> > Regards,
> >
> > Peo Sjoblom
> >
> > (No private emails please)
> >
> >
> > "Jeanette" <Jeanette@discussions.microsoft.com> wrote in message
> > news:13CB639A-CBC6-478F-A290-802D0A3AF5F0@microsoft.com...
> > >I know there is a simple solution to my problem. The following is my
> > >formula:
> > >
> > > =COUNT(IF((TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501")*(IndexData!$A$5:$A$23309="AUT"),AND(IndexData!$I$5:$I$23309<=3,""),IndexData!$I$5:$I$23309))
> > >
> > > The formula is not bringing back to the number of "AUT" less than or equal
> > > to 3, if it were the number would be Zero, instead it is coming back with
> > > 23304 for the month of January when there were no "AUT" completed in less
> > > than 19 days.
> > >
> > > What have I done and how do I fix it? I also need the # of "AUT" between
> > > 4
> > > and 5 and Greater than 6.
> >
> >

Thanks ~ now how do I write --(indexData!$I$5:$I$23309>=4<=5) and have it
work correctly?

"Peo Sjoblom" wrote:

> =SUMPRODUCT(--(TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501"),--(IndexData!$A$5:$A$23309="AUT"),--(IndexData!$I$5:$I$23309<=3))
>
>
> --
> Regards,
>
> Peo Sjoblom
>
> (No private emails please)
>
>
> "Jeanette" <Jeanette@discussions.microsoft.com> wrote in message
> news:13CB639A-CBC6-478F-A290-802D0A3AF5F0@microsoft.com...
> >I know there is a simple solution to my problem. The following is my
> >formula:
> >
> > =COUNT(IF((TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501")*(IndexData!$A$5:$A$23309="AUT"),AND(IndexData!$I$5:$I$23309<=3,""),IndexData!$I$5:$I$23309))
> >
> > The formula is not bringing back to the number of "AUT" less than or equal
> > to 3, if it were the number would be Zero, instead it is coming back with
> > 23304 for the month of January when there were no "AUT" completed in less
> > than 19 days.
> >
> > What have I done and how do I fix it? I also need the # of "AUT" between
> > 4
> > and 5 and Greater than 6.
>
>

=SUMPRODUCT(--(TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501"),--(IndexData!$A$5:$A$23309="AUT"),--(IndexData!$I$5:$I$23309<=3))


--
Regards,

Peo Sjoblom

(No private emails please)


"Jeanette" <Jeanette@discussions.microsoft.com> wrote in message
news:13CB639A-CBC6-478F-A290-802D0A3AF5F0@microsoft.com...
>I know there is a simple solution to my problem. The following is my
>formula:
>
> =COUNT(IF((TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501")*(IndexData!$A$5:$A$23309="AUT"),AND(IndexData!$I$5:$I$23309<=3,""),IndexData!$I$5:$I$23309))
>
> The formula is not bringing back to the number of "AUT" less than or equal
> to 3, if it were the number would be Zero, instead it is coming back with
> 23304 for the month of January when there were no "AUT" completed in less
> than 19 days.
>
> What have I done and how do I fix it? I also need the # of "AUT" between
> 4
> and 5 and Greater than 6.

Thanks ~ now how do I write --(indexData!$I$5:$I$23309>=4<=5) and have it
work correctly?

"Peo Sjoblom" wrote:

> =SUMPRODUCT(--(TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501"),--(IndexData!$A$5:$A$23309="AUT"),--(IndexData!$I$5:$I$23309<=3))
>
>
> --
> Regards,
>
> Peo Sjoblom
>
> (No private emails please)
>
>
> "Jeanette" <Jeanette@discussions.microsoft.com> wrote in message
> news:13CB639A-CBC6-478F-A290-802D0A3AF5F0@microsoft.com...
> >I know there is a simple solution to my problem. The following is my
> >formula:
> >
> > =COUNT(IF((TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501")*(IndexData!$A$5:$A$23309="AUT"),AND(IndexData!$I$5:$I$23309<=3,""),IndexData!$I$5:$I$23309))
> >
> > The formula is not bringing back to the number of "AUT" less than or equal
> > to 3, if it were the number would be Zero, instead it is coming back with
> > 23304 for the month of January when there were no "AUT" completed in less
> > than 19 days.
> >
> > What have I done and how do I fix it? I also need the # of "AUT" between
> > 4
> > and 5 and Greater than 6.
>
>

Since you want the AND of the conditions >=4, <=5 all you do is split it into
two factors

--(indexData!$I$5:$I$23309>=4),--(indexData!$I$5:$I$23309<=5)

Alok

"Jeanette" wrote:

> Thanks ~ now how do I write --(indexData!$I$5:$I$23309>=4<=5) and have it
> work correctly?
>
> "Peo Sjoblom" wrote:
>
> > =SUMPRODUCT(--(TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501"),--(IndexData!$A$5:$A$23309="AUT"),--(IndexData!$I$5:$I$23309<=3))
> >
> >
> > --
> > Regards,
> >
> > Peo Sjoblom
> >
> > (No private emails please)
> >
> >
> > "Jeanette" <Jeanette@discussions.microsoft.com> wrote in message
> > news:13CB639A-CBC6-478F-A290-802D0A3AF5F0@microsoft.com...
> > >I know there is a simple solution to my problem. The following is my
> > >formula:
> > >
> > > =COUNT(IF((TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501")*(IndexData!$A$5:$A$23309="AUT"),AND(IndexData!$I$5:$I$23309<=3,""),IndexData!$I$5:$I$23309))
> > >
> > > The formula is not bringing back to the number of "AUT" less than or equal
> > > to 3, if it were the number would be Zero, instead it is coming back with
> > > 23304 for the month of January when there were no "AUT" completed in less
> > > than 19 days.
> > >
> > > What have I done and how do I fix it? I also need the # of "AUT" between
> > > 4
> > > and 5 and Greater than 6.
> >
> >

Thanks ~ I was including "and" so it wasn't working

"Alok" wrote:

> Since you want the AND of the conditions >=4, <=5 all you do is split it into
> two factors
>
> --(indexData!$I$5:$I$23309>=4),--(indexData!$I$5:$I$23309<=5)
>
> Alok
>
> "Jeanette" wrote:
>
> > Thanks ~ now how do I write --(indexData!$I$5:$I$23309>=4<=5) and have it
> > work correctly?
> >
> > "Peo Sjoblom" wrote:
> >
> > > =SUMPRODUCT(--(TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501"),--(IndexData!$A$5:$A$23309="AUT"),--(IndexData!$I$5:$I$23309<=3))
> > >
> > >
> > > --
> > > Regards,
> > >
> > > Peo Sjoblom
> > >
> > > (No private emails please)
> > >
> > >
> > > "Jeanette" <Jeanette@discussions.microsoft.com> wrote in message
> > > news:13CB639A-CBC6-478F-A290-802D0A3AF5F0@microsoft.com...
> > > >I know there is a simple solution to my problem. The following is my
> > > >formula:
> > > >
> > > > =COUNT(IF((TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501")*(IndexData!$A$5:$A$23309="AUT"),AND(IndexData!$I$5:$I$23309<=3,""),IndexData!$I$5:$I$23309))
> > > >
> > > > The formula is not bringing back to the number of "AUT" less than or equal
> > > > to 3, if it were the number would be Zero, instead it is coming back with
> > > > 23304 for the month of January when there were no "AUT" completed in less
> > > > than 19 days.
> > > >
> > > > What have I done and how do I fix it? I also need the # of "AUT" between
> > > > 4
> > > > and 5 and Greater than 6.
> > >
> > >

Thanks ~ now how do I write --(indexData!$I$5:$I$23309>=4<=5) and have it
work correctly?

"Peo Sjoblom" wrote:

> =SUMPRODUCT(--(TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501"),--(IndexData!$A$5:$A$23309="AUT"),--(IndexData!$I$5:$I$23309<=3))
>
>
> --
> Regards,
>
> Peo Sjoblom
>
> (No private emails please)
>
>
> "Jeanette" <Jeanette@discussions.microsoft.com> wrote in message
> news:13CB639A-CBC6-478F-A290-802D0A3AF5F0@microsoft.com...
> >I know there is a simple solution to my problem. The following is my
> >formula:
> >
> > =COUNT(IF((TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501")*(IndexData!$A$5:$A$23309="AUT"),AND(IndexData!$I$5:$I$23309<=3,""),IndexData!$I$5:$I$23309))
> >
> > The formula is not bringing back to the number of "AUT" less than or equal
> > to 3, if it were the number would be Zero, instead it is coming back with
> > 23304 for the month of January when there were no "AUT" completed in less
> > than 19 days.
> >
> > What have I done and how do I fix it? I also need the # of "AUT" between
> > 4
> > and 5 and Greater than 6.
>
>

Since you want the AND of the conditions >=4, <=5 all you do is split it into
two factors

--(indexData!$I$5:$I$23309>=4),--(indexData!$I$5:$I$23309<=5)

Alok

"Jeanette" wrote:

> Thanks ~ now how do I write --(indexData!$I$5:$I$23309>=4<=5) and have it
> work correctly?
>
> "Peo Sjoblom" wrote:
>
> > =SUMPRODUCT(--(TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501"),--(IndexData!$A$5:$A$23309="AUT"),--(IndexData!$I$5:$I$23309<=3))
> >
> >
> > --
> > Regards,
> >
> > Peo Sjoblom
> >
> > (No private emails please)
> >
> >
> > "Jeanette" <Jeanette@discussions.microsoft.com> wrote in message
> > news:13CB639A-CBC6-478F-A290-802D0A3AF5F0@microsoft.com...
> > >I know there is a simple solution to my problem. The following is my
> > >formula:
> > >
> > > =COUNT(IF((TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501")*(IndexData!$A$5:$A$23309="AUT"),AND(IndexData!$I$5:$I$23309<=3,""),IndexData!$I$5:$I$23309))
> > >
> > > The formula is not bringing back to the number of "AUT" less than or equal
> > > to 3, if it were the number would be Zero, instead it is coming back with
> > > 23304 for the month of January when there were no "AUT" completed in less
> > > than 19 days.
> > >
> > > What have I done and how do I fix it? I also need the # of "AUT" between
> > > 4
> > > and 5 and Greater than 6.
> >
> >

=SUMPRODUCT(--(TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501"),--(IndexData!$A$5:$A$23309="AUT"),--(IndexData!$I$5:$I$23309<=3))


--
Regards,

Peo Sjoblom

(No private emails please)


"Jeanette" <Jeanette@discussions.microsoft.com> wrote in message
news:13CB639A-CBC6-478F-A290-802D0A3AF5F0@microsoft.com...
>I know there is a simple solution to my problem. The following is my
>formula:
>
> =COUNT(IF((TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501")*(IndexData!$A$5:$A$23309="AUT"),AND(IndexData!$I$5:$I$23309<=3,""),IndexData!$I$5:$I$23309))
>
> The formula is not bringing back to the number of "AUT" less than or equal
> to 3, if it were the number would be Zero, instead it is coming back with
> 23304 for the month of January when there were no "AUT" completed in less
> than 19 days.
>
> What have I done and how do I fix it? I also need the # of "AUT" between
> 4
> and 5 and Greater than 6.

Thanks ~ I was including "and" so it wasn't working

"Alok" wrote:

> Since you want the AND of the conditions >=4, <=5 all you do is split it into
> two factors
>
> --(indexData!$I$5:$I$23309>=4),--(indexData!$I$5:$I$23309<=5)
>
> Alok
>
> "Jeanette" wrote:
>
> > Thanks ~ now how do I write --(indexData!$I$5:$I$23309>=4<=5) and have it
> > work correctly?
> >
> > "Peo Sjoblom" wrote:
> >
> > > =SUMPRODUCT(--(TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501"),--(IndexData!$A$5:$A$23309="AUT"),--(IndexData!$I$5:$I$23309<=3))
> > >
> > >
> > > --
> > > Regards,
> > >
> > > Peo Sjoblom
> > >
> > > (No private emails please)
> > >
> > >
> > > "Jeanette" <Jeanette@discussions.microsoft.com> wrote in message
> > > news:13CB639A-CBC6-478F-A290-802D0A3AF5F0@microsoft.com...
> > > >I know there is a simple solution to my problem. The following is my
> > > >formula:
> > > >
> > > > =COUNT(IF((TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501")*(IndexData!$A$5:$A$23309="AUT"),AND(IndexData!$I$5:$I$23309<=3,""),IndexData!$I$5:$I$23309))
> > > >
> > > > The formula is not bringing back to the number of "AUT" less than or equal
> > > > to 3, if it were the number would be Zero, instead it is coming back with
> > > > 23304 for the month of January when there were no "AUT" completed in less
> > > > than 19 days.
> > > >
> > > > What have I done and how do I fix it? I also need the # of "AUT" between
> > > > 4
> > > > and 5 and Greater than 6.
> > >
> > >

Thanks ~ now how do I write --(indexData!$I$5:$I$23309>=4<=5) and have it
work correctly?

"Peo Sjoblom" wrote:

> =SUMPRODUCT(--(TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501"),--(IndexData!$A$5:$A$23309="AUT"),--(IndexData!$I$5:$I$23309<=3))
>
>
> --
> Regards,
>
> Peo Sjoblom
>
> (No private emails please)
>
>
> "Jeanette" <Jeanette@discussions.microsoft.com> wrote in message
> news:13CB639A-CBC6-478F-A290-802D0A3AF5F0@microsoft.com...
> >I know there is a simple solution to my problem. The following is my
> >formula:
> >
> > =COUNT(IF((TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501")*(IndexData!$A$5:$A$23309="AUT"),AND(IndexData!$I$5:$I$23309<=3,""),IndexData!$I$5:$I$23309))
> >
> > The formula is not bringing back to the number of "AUT" less than or equal
> > to 3, if it were the number would be Zero, instead it is coming back with
> > 23304 for the month of January when there were no "AUT" completed in less
> > than 19 days.
> >
> > What have I done and how do I fix it? I also need the # of "AUT" between
> > 4
> > and 5 and Greater than 6.
>
>

=SUMPRODUCT(--(TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501"),--(IndexData!$A$5:$A$23309="AUT"),--(IndexData!$I$5:$I$23309<=3))


--
Regards,

Peo Sjoblom

(No private emails please)


"Jeanette" <Jeanette@discussions.microsoft.com> wrote in message
news:13CB639A-CBC6-478F-A290-802D0A3AF5F0@microsoft.com...
>I know there is a simple solution to my problem. The following is my
>formula:
>
> =COUNT(IF((TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501")*(IndexData!$A$5:$A$23309="AUT"),AND(IndexData!$I$5:$I$23309<=3,""),IndexData!$I$5:$I$23309))
>
> The formula is not bringing back to the number of "AUT" less than or equal
> to 3, if it were the number would be Zero, instead it is coming back with
> 23304 for the month of January when there were no "AUT" completed in less
> than 19 days.
>
> What have I done and how do I fix it? I also need the # of "AUT" between
> 4
> and 5 and Greater than 6.

Since you want the AND of the conditions >=4, <=5 all you do is split it into
two factors

--(indexData!$I$5:$I$23309>=4),--(indexData!$I$5:$I$23309<=5)

Alok

"Jeanette" wrote:

> Thanks ~ now how do I write --(indexData!$I$5:$I$23309>=4<=5) and have it
> work correctly?
>
> "Peo Sjoblom" wrote:
>
> > =SUMPRODUCT(--(TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501"),--(IndexData!$A$5:$A$23309="AUT"),--(IndexData!$I$5:$I$23309<=3))
> >
> >
> > --
> > Regards,
> >
> > Peo Sjoblom
> >
> > (No private emails please)
> >
> >
> > "Jeanette" <Jeanette@discussions.microsoft.com> wrote in message
> > news:13CB639A-CBC6-478F-A290-802D0A3AF5F0@microsoft.com...
> > >I know there is a simple solution to my problem. The following is my
> > >formula:
> > >
> > > =COUNT(IF((TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501")*(IndexData!$A$5:$A$23309="AUT"),AND(IndexData!$I$5:$I$23309<=3,""),IndexData!$I$5:$I$23309))
> > >
> > > The formula is not bringing back to the number of "AUT" less than or equal
> > > to 3, if it were the number would be Zero, instead it is coming back with
> > > 23304 for the month of January when there were no "AUT" completed in less
> > > than 19 days.
> > >
> > > What have I done and how do I fix it? I also need the # of "AUT" between
> > > 4
> > > and 5 and Greater than 6.
> >
> >

I know there is a simple solution to my problem. The following is my formula:

=COUNT(IF((TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501")*(IndexData!$A$5:$A$23309="AUT"),AND(IndexData!$I$5:$I$23309<=3,""),IndexData!$I$5:$I$23309))

The formula is not bringing back to the number of "AUT" less than or equal
to 3, if it were the number would be Zero, instead it is coming back with
23304 for the month of January when there were no "AUT" completed in less
than 19 days.

What have I done and how do I fix it? I also need the # of "AUT" between 4
and 5 and Greater than 6.

Thanks ~ I was including "and" so it wasn't working

"Alok" wrote:

> Since you want the AND of the conditions >=4, <=5 all you do is split it into
> two factors
>
> --(indexData!$I$5:$I$23309>=4),--(indexData!$I$5:$I$23309<=5)
>
> Alok
>
> "Jeanette" wrote:
>
> > Thanks ~ now how do I write --(indexData!$I$5:$I$23309>=4<=5) and have it
> > work correctly?
> >
> > "Peo Sjoblom" wrote:
> >
> > > =SUMPRODUCT(--(TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501"),--(IndexData!$A$5:$A$23309="AUT"),--(IndexData!$I$5:$I$23309<=3))
> > >
> > >
> > > --
> > > Regards,
> > >
> > > Peo Sjoblom
> > >
> > > (No private emails please)
> > >
> > >
> > > "Jeanette" <Jeanette@discussions.microsoft.com> wrote in message
> > > news:13CB639A-CBC6-478F-A290-802D0A3AF5F0@microsoft.com...
> > > >I know there is a simple solution to my problem. The following is my
> > > >formula:
> > > >
> > > > =COUNT(IF((TEXT(IndexData!$G$5:$G$23309,"yyyymm")="200501")*(IndexData!$A$5:$A$23309="AUT"),AND(IndexData!$I$5:$I$23309<=3,""),IndexData!$I$5:$I$23309))
> > > >
> > > > The formula is not bringing back to the number of "AUT" less than or equal
> > > > to 3, if it were the number would be Zero, instead it is coming back with
> > > > 23304 for the month of January when there were no "AUT" completed in less
> > > > than 19 days.
> > > >
> > > > What have I done and how do I fix it? I also need the # of "AUT" between
> > > > 4
> > > > and 5 and Greater than 6.
> > >
> > >