count problem

Hi vipa,

Try this:

=COUNT($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")

Regards,
KL


"vipa2000" <vipa2000@discussions.microsoft.com> wrote in message
news:4C1619BC-76CB-4A14-A4A7-D96D725B07A9@microsoft.com...
>I am trying to setup a spreadsheet where data will be imported in from
> another package into a sheet within the spreadsheet on a monthly basis. On
> another sheet I am trying to setup various formula that will analyse this
> data automatically. Normally i would use Access but other people within
> the
> company do not understand Access. The issue:-
>
> I can't get the count function or any derivative to do what i want.
> Example
> of problem below.
> A b c d
> e
> 120873 50183368 6 blank REL NMAT PRC SETC
> 120873 50183299 4 blank CLSD PRT NMAT PRC
> 122873 50185108 3 blank PCNF NMAT PRC SETC
> 122873 50178522 3 HUDS002277 TECO PCNF PRT NMAT
> 122873 50147658 5 HUDS003115 REL NMAT PRC SETC
>
> I want to total the no of records in column A but deduct those records in
> column d where a record is found with a hud prefix
>
> --
> Regards vipa

Thanks KL. worked fine.
--
Regards vipa


"KL" wrote:

> sorry, actually meant this:
>
> =COUNTA($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")
>
> KL
>
>
>
> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
> news:OnhGZHvkFHA.3960@TK2MSFTNGP12.phx.gbl...
> > Hi vipa,
> >
> > Try this:
> >
> > =COUNT($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")
> >
> > Regards,
> > KL
> >
> >
> > "vipa2000" <vipa2000@discussions.microsoft.com> wrote in message
> > news:4C1619BC-76CB-4A14-A4A7-D96D725B07A9@microsoft.com...
> >>I am trying to setup a spreadsheet where data will be imported in from
> >> another package into a sheet within the spreadsheet on a monthly basis.
> >> On
> >> another sheet I am trying to setup various formula that will analyse this
> >> data automatically. Normally i would use Access but other people within
> >> the
> >> company do not understand Access. The issue:-
> >>
> >> I can't get the count function or any derivative to do what i want.
> >> Example
> >> of problem below.
> >> A b c d e
> >> 120873 50183368 6 blank REL NMAT PRC SETC
> >> 120873 50183299 4 blank CLSD PRT NMAT PRC
> >> 122873 50185108 3 blank PCNF NMAT PRC SETC
> >> 122873 50178522 3 HUDS002277 TECO PCNF PRT NMAT
> >> 122873 50147658 5 HUDS003115 REL NMAT PRC SETC
> >>
> >> I want to total the no of records in column A but deduct those records in
> >> column d where a record is found with a hud prefix
> >>
> >> --
> >> Regards vipa
> >
> >
>
>
>

Seeing Chuck's suggestion made me realise that I had misread the request:

=SUMPRODUCT(--(A1:A5<>""),--(LEFT(D1:D5,3)<>"HUD"))

--
HTH

Sandy
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk


"Sandy Mann" <sandymann2@mailinator.com> wrote in message
news:Oef0nMvkFHA.3200@TK2MSFTNGP10.phx.gbl...
> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
> news:OnhGZHvkFHA.3960@TK2MSFTNGP12.phx.gbl...
>> Hi vipa,
>>
>> Try this:
>>
>> =COUNT($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")
>
> Assumes that every entry in column D with "Hud*" has a corresponding entry
> in Column A which may or may not be true. I would suggest:
>
> =SUMPRODUCT(--(A1:A5<>""),--(LEFT(D1:D5,3)="HUD"))
> --
> HTH
>
> Sandy
> sandymann@mailinator.com
> Replace@mailinator with @tiscali.co.uk
>
>
>

sorry, actually meant this:

=COUNTA($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")

KL



"KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
news:OnhGZHvkFHA.3960@TK2MSFTNGP12.phx.gbl...
> Hi vipa,
>
> Try this:
>
> =COUNT($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")
>
> Regards,
> KL
>
>
> "vipa2000" <vipa2000@discussions.microsoft.com> wrote in message
> news:4C1619BC-76CB-4A14-A4A7-D96D725B07A9@microsoft.com...
>>I am trying to setup a spreadsheet where data will be imported in from
>> another package into a sheet within the spreadsheet on a monthly basis.
>> On
>> another sheet I am trying to setup various formula that will analyse this
>> data automatically. Normally i would use Access but other people within
>> the
>> company do not understand Access. The issue:-
>>
>> I can't get the count function or any derivative to do what i want.
>> Example
>> of problem below.
>> A b c d e
>> 120873 50183368 6 blank REL NMAT PRC SETC
>> 120873 50183299 4 blank CLSD PRT NMAT PRC
>> 122873 50185108 3 blank PCNF NMAT PRC SETC
>> 122873 50178522 3 HUDS002277 TECO PCNF PRT NMAT
>> 122873 50147658 5 HUDS003115 REL NMAT PRC SETC
>>
>> I want to total the no of records in column A but deduct those records in
>> column d where a record is found with a hud prefix
>>
>> --
>> Regards vipa
>
>

"KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
news:OnhGZHvkFHA.3960@TK2MSFTNGP12.phx.gbl...
> Hi vipa,
>
> Try this:
>
> =COUNT($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")

Assumes that every entry in column D with "Hud*" has a corresponding entry
in Column A which may or may not be true. I would suggest:

=SUMPRODUCT(--(A1:A5<>""),--(LEFT(D1:D5,3)="HUD"))
--
HTH

Sandy
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk

One way would be to Autofilter > Custom > "does not equal" >
HUD*..........and then use =SUBTOTAL,2,A:A)

Vaya con Dios,
Chuck CABGx3



"vipa2000" <vipa2000@discussions.microsoft.com> wrote in message
news:4C1619BC-76CB-4A14-A4A7-D96D725B07A9@microsoft.com...
> I am trying to setup a spreadsheet where data will be imported in from
> another package into a sheet within the spreadsheet on a monthly basis. On
> another sheet I am trying to setup various formula that will analyse this
> data automatically. Normally i would use Access but other people within
the
> company do not understand Access. The issue:-
>
> I can't get the count function or any derivative to do what i want.
Example
> of problem below.
> A b c d
e
> 120873 50183368 6 blank REL NMAT PRC SETC
> 120873 50183299 4 blank CLSD PRT NMAT PRC
> 122873 50185108 3 blank PCNF NMAT PRC SETC
> 122873 50178522 3 HUDS002277 TECO PCNF PRT NMAT
> 122873 50147658 5 HUDS003115 REL NMAT PRC SETC
>
> I want to total the no of records in column A but deduct those records in
> column d where a record is found with a hud prefix
>
> --
> Regards vipa

Thanks KL. worked fine.
--
Regards vipa


"KL" wrote:

> sorry, actually meant this:
>
> =COUNTA($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")
>
> KL
>
>
>
> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
> news:OnhGZHvkFHA.3960@TK2MSFTNGP12.phx.gbl...
> > Hi vipa,
> >
> > Try this:
> >
> > =COUNT($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")
> >
> > Regards,
> > KL
> >
> >
> > "vipa2000" <vipa2000@discussions.microsoft.com> wrote in message
> > news:4C1619BC-76CB-4A14-A4A7-D96D725B07A9@microsoft.com...
> >>I am trying to setup a spreadsheet where data will be imported in from
> >> another package into a sheet within the spreadsheet on a monthly basis.
> >> On
> >> another sheet I am trying to setup various formula that will analyse this
> >> data automatically. Normally i would use Access but other people within
> >> the
> >> company do not understand Access. The issue:-
> >>
> >> I can't get the count function or any derivative to do what i want.
> >> Example
> >> of problem below.
> >> A b c d e
> >> 120873 50183368 6 blank REL NMAT PRC SETC
> >> 120873 50183299 4 blank CLSD PRT NMAT PRC
> >> 122873 50185108 3 blank PCNF NMAT PRC SETC
> >> 122873 50178522 3 HUDS002277 TECO PCNF PRT NMAT
> >> 122873 50147658 5 HUDS003115 REL NMAT PRC SETC
> >>
> >> I want to total the no of records in column A but deduct those records in
> >> column d where a record is found with a hud prefix
> >>
> >> --
> >> Regards vipa
> >
> >
>
>
>

Hi vipa,

Try this:

=COUNT($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")

Regards,
KL


"vipa2000" <vipa2000@discussions.microsoft.com> wrote in message
news:4C1619BC-76CB-4A14-A4A7-D96D725B07A9@microsoft.com...
>I am trying to setup a spreadsheet where data will be imported in from
> another package into a sheet within the spreadsheet on a monthly basis. On
> another sheet I am trying to setup various formula that will analyse this
> data automatically. Normally i would use Access but other people within
> the
> company do not understand Access. The issue:-
>
> I can't get the count function or any derivative to do what i want.
> Example
> of problem below.
> A b c d
> e
> 120873 50183368 6 blank REL NMAT PRC SETC
> 120873 50183299 4 blank CLSD PRT NMAT PRC
> 122873 50185108 3 blank PCNF NMAT PRC SETC
> 122873 50178522 3 HUDS002277 TECO PCNF PRT NMAT
> 122873 50147658 5 HUDS003115 REL NMAT PRC SETC
>
> I want to total the no of records in column A but deduct those records in
> column d where a record is found with a hud prefix
>
> --
> Regards vipa

Seeing Chuck's suggestion made me realise that I had misread the request:

=SUMPRODUCT(--(A1:A5<>""),--(LEFT(D1:D5,3)<>"HUD"))

--
HTH

Sandy
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk


"Sandy Mann" <sandymann2@mailinator.com> wrote in message
news:Oef0nMvkFHA.3200@TK2MSFTNGP10.phx.gbl...
> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
> news:OnhGZHvkFHA.3960@TK2MSFTNGP12.phx.gbl...
>> Hi vipa,
>>
>> Try this:
>>
>> =COUNT($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")
>
> Assumes that every entry in column D with "Hud*" has a corresponding entry
> in Column A which may or may not be true. I would suggest:
>
> =SUMPRODUCT(--(A1:A5<>""),--(LEFT(D1:D5,3)="HUD"))
> --
> HTH
>
> Sandy
> sandymann@mailinator.com
> Replace@mailinator with @tiscali.co.uk
>
>
>

One way would be to Autofilter > Custom > "does not equal" >
HUD*..........and then use =SUBTOTAL,2,A:A)

Vaya con Dios,
Chuck CABGx3



"vipa2000" <vipa2000@discussions.microsoft.com> wrote in message
news:4C1619BC-76CB-4A14-A4A7-D96D725B07A9@microsoft.com...
> I am trying to setup a spreadsheet where data will be imported in from
> another package into a sheet within the spreadsheet on a monthly basis. On
> another sheet I am trying to setup various formula that will analyse this
> data automatically. Normally i would use Access but other people within
the
> company do not understand Access. The issue:-
>
> I can't get the count function or any derivative to do what i want.
Example
> of problem below.
> A b c d
e
> 120873 50183368 6 blank REL NMAT PRC SETC
> 120873 50183299 4 blank CLSD PRT NMAT PRC
> 122873 50185108 3 blank PCNF NMAT PRC SETC
> 122873 50178522 3 HUDS002277 TECO PCNF PRT NMAT
> 122873 50147658 5 HUDS003115 REL NMAT PRC SETC
>
> I want to total the no of records in column A but deduct those records in
> column d where a record is found with a hud prefix
>
> --
> Regards vipa

sorry, actually meant this:

=COUNTA($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")

KL



"KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
news:OnhGZHvkFHA.3960@TK2MSFTNGP12.phx.gbl...
> Hi vipa,
>
> Try this:
>
> =COUNT($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")
>
> Regards,
> KL
>
>
> "vipa2000" <vipa2000@discussions.microsoft.com> wrote in message
> news:4C1619BC-76CB-4A14-A4A7-D96D725B07A9@microsoft.com...
>>I am trying to setup a spreadsheet where data will be imported in from
>> another package into a sheet within the spreadsheet on a monthly basis.
>> On
>> another sheet I am trying to setup various formula that will analyse this
>> data automatically. Normally i would use Access but other people within
>> the
>> company do not understand Access. The issue:-
>>
>> I can't get the count function or any derivative to do what i want.
>> Example
>> of problem below.
>> A b c d e
>> 120873 50183368 6 blank REL NMAT PRC SETC
>> 120873 50183299 4 blank CLSD PRT NMAT PRC
>> 122873 50185108 3 blank PCNF NMAT PRC SETC
>> 122873 50178522 3 HUDS002277 TECO PCNF PRT NMAT
>> 122873 50147658 5 HUDS003115 REL NMAT PRC SETC
>>
>> I want to total the no of records in column A but deduct those records in
>> column d where a record is found with a hud prefix
>>
>> --
>> Regards vipa
>
>

"KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
news:OnhGZHvkFHA.3960@TK2MSFTNGP12.phx.gbl...
> Hi vipa,
>
> Try this:
>
> =COUNT($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")

Assumes that every entry in column D with "Hud*" has a corresponding entry
in Column A which may or may not be true. I would suggest:

=SUMPRODUCT(--(A1:A5<>""),--(LEFT(D1:D5,3)="HUD"))
--
HTH

Sandy
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk

Thanks KL. worked fine.
--
Regards vipa


"KL" wrote:

> sorry, actually meant this:
>
> =COUNTA($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")
>
> KL
>
>
>
> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
> news:OnhGZHvkFHA.3960@TK2MSFTNGP12.phx.gbl...
> > Hi vipa,
> >
> > Try this:
> >
> > =COUNT($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")
> >
> > Regards,
> > KL
> >
> >
> > "vipa2000" <vipa2000@discussions.microsoft.com> wrote in message
> > news:4C1619BC-76CB-4A14-A4A7-D96D725B07A9@microsoft.com...
> >>I am trying to setup a spreadsheet where data will be imported in from
> >> another package into a sheet within the spreadsheet on a monthly basis.
> >> On
> >> another sheet I am trying to setup various formula that will analyse this
> >> data automatically. Normally i would use Access but other people within
> >> the
> >> company do not understand Access. The issue:-
> >>
> >> I can't get the count function or any derivative to do what i want.
> >> Example
> >> of problem below.
> >> A b c d e
> >> 120873 50183368 6 blank REL NMAT PRC SETC
> >> 120873 50183299 4 blank CLSD PRT NMAT PRC
> >> 122873 50185108 3 blank PCNF NMAT PRC SETC
> >> 122873 50178522 3 HUDS002277 TECO PCNF PRT NMAT
> >> 122873 50147658 5 HUDS003115 REL NMAT PRC SETC
> >>
> >> I want to total the no of records in column A but deduct those records in
> >> column d where a record is found with a hud prefix
> >>
> >> --
> >> Regards vipa
> >
> >
>
>
>

Seeing Chuck's suggestion made me realise that I had misread the request:

=SUMPRODUCT(--(A1:A5<>""),--(LEFT(D1:D5,3)<>"HUD"))

--
HTH

Sandy
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk


"Sandy Mann" <sandymann2@mailinator.com> wrote in message
news:Oef0nMvkFHA.3200@TK2MSFTNGP10.phx.gbl...
> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
> news:OnhGZHvkFHA.3960@TK2MSFTNGP12.phx.gbl...
>> Hi vipa,
>>
>> Try this:
>>
>> =COUNT($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")
>
> Assumes that every entry in column D with "Hud*" has a corresponding entry
> in Column A which may or may not be true. I would suggest:
>
> =SUMPRODUCT(--(A1:A5<>""),--(LEFT(D1:D5,3)="HUD"))
> --
> HTH
>
> Sandy
> sandymann@mailinator.com
> Replace@mailinator with @tiscali.co.uk
>
>
>

One way would be to Autofilter > Custom > "does not equal" >
HUD*..........and then use =SUBTOTAL,2,A:A)

Vaya con Dios,
Chuck CABGx3



"vipa2000" <vipa2000@discussions.microsoft.com> wrote in message
news:4C1619BC-76CB-4A14-A4A7-D96D725B07A9@microsoft.com...
> I am trying to setup a spreadsheet where data will be imported in from
> another package into a sheet within the spreadsheet on a monthly basis. On
> another sheet I am trying to setup various formula that will analyse this
> data automatically. Normally i would use Access but other people within
the
> company do not understand Access. The issue:-
>
> I can't get the count function or any derivative to do what i want.
Example
> of problem below.
> A b c d
e
> 120873 50183368 6 blank REL NMAT PRC SETC
> 120873 50183299 4 blank CLSD PRT NMAT PRC
> 122873 50185108 3 blank PCNF NMAT PRC SETC
> 122873 50178522 3 HUDS002277 TECO PCNF PRT NMAT
> 122873 50147658 5 HUDS003115 REL NMAT PRC SETC
>
> I want to total the no of records in column A but deduct those records in
> column d where a record is found with a hud prefix
>
> --
> Regards vipa

"KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
news:OnhGZHvkFHA.3960@TK2MSFTNGP12.phx.gbl...
> Hi vipa,
>
> Try this:
>
> =COUNT($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")

Assumes that every entry in column D with "Hud*" has a corresponding entry
in Column A which may or may not be true. I would suggest:

=SUMPRODUCT(--(A1:A5<>""),--(LEFT(D1:D5,3)="HUD"))
--
HTH

Sandy
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk

sorry, actually meant this:

=COUNTA($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")

KL



"KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
news:OnhGZHvkFHA.3960@TK2MSFTNGP12.phx.gbl...
> Hi vipa,
>
> Try this:
>
> =COUNT($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")
>
> Regards,
> KL
>
>
> "vipa2000" <vipa2000@discussions.microsoft.com> wrote in message
> news:4C1619BC-76CB-4A14-A4A7-D96D725B07A9@microsoft.com...
>>I am trying to setup a spreadsheet where data will be imported in from
>> another package into a sheet within the spreadsheet on a monthly basis.
>> On
>> another sheet I am trying to setup various formula that will analyse this
>> data automatically. Normally i would use Access but other people within
>> the
>> company do not understand Access. The issue:-
>>
>> I can't get the count function or any derivative to do what i want.
>> Example
>> of problem below.
>> A b c d e
>> 120873 50183368 6 blank REL NMAT PRC SETC
>> 120873 50183299 4 blank CLSD PRT NMAT PRC
>> 122873 50185108 3 blank PCNF NMAT PRC SETC
>> 122873 50178522 3 HUDS002277 TECO PCNF PRT NMAT
>> 122873 50147658 5 HUDS003115 REL NMAT PRC SETC
>>
>> I want to total the no of records in column A but deduct those records in
>> column d where a record is found with a hud prefix
>>
>> --
>> Regards vipa
>
>

Hi vipa,

Try this:

=COUNT($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")

Regards,
KL


"vipa2000" <vipa2000@discussions.microsoft.com> wrote in message
news:4C1619BC-76CB-4A14-A4A7-D96D725B07A9@microsoft.com...
>I am trying to setup a spreadsheet where data will be imported in from
> another package into a sheet within the spreadsheet on a monthly basis. On
> another sheet I am trying to setup various formula that will analyse this
> data automatically. Normally i would use Access but other people within
> the
> company do not understand Access. The issue:-
>
> I can't get the count function or any derivative to do what i want.
> Example
> of problem below.
> A b c d
> e
> 120873 50183368 6 blank REL NMAT PRC SETC
> 120873 50183299 4 blank CLSD PRT NMAT PRC
> 122873 50185108 3 blank PCNF NMAT PRC SETC
> 122873 50178522 3 HUDS002277 TECO PCNF PRT NMAT
> 122873 50147658 5 HUDS003115 REL NMAT PRC SETC
>
> I want to total the no of records in column A but deduct those records in
> column d where a record is found with a hud prefix
>
> --
> Regards vipa

"KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
news:OnhGZHvkFHA.3960@TK2MSFTNGP12.phx.gbl...
> Hi vipa,
>
> Try this:
>
> =COUNT($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")

Assumes that every entry in column D with "Hud*" has a corresponding entry
in Column A which may or may not be true. I would suggest:

=SUMPRODUCT(--(A1:A5<>""),--(LEFT(D1:D5,3)="HUD"))
--
HTH

Sandy
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk

One way would be to Autofilter > Custom > "does not equal" >
HUD*..........and then use =SUBTOTAL,2,A:A)

Vaya con Dios,
Chuck CABGx3



"vipa2000" <vipa2000@discussions.microsoft.com> wrote in message
news:4C1619BC-76CB-4A14-A4A7-D96D725B07A9@microsoft.com...
> I am trying to setup a spreadsheet where data will be imported in from
> another package into a sheet within the spreadsheet on a monthly basis. On
> another sheet I am trying to setup various formula that will analyse this
> data automatically. Normally i would use Access but other people within
the
> company do not understand Access. The issue:-
>
> I can't get the count function or any derivative to do what i want.
Example
> of problem below.
> A b c d
e
> 120873 50183368 6 blank REL NMAT PRC SETC
> 120873 50183299 4 blank CLSD PRT NMAT PRC
> 122873 50185108 3 blank PCNF NMAT PRC SETC
> 122873 50178522 3 HUDS002277 TECO PCNF PRT NMAT
> 122873 50147658 5 HUDS003115 REL NMAT PRC SETC
>
> I want to total the no of records in column A but deduct those records in
> column d where a record is found with a hud prefix
>
> --
> Regards vipa

sorry, actually meant this:

=COUNTA($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")

KL



"KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
news:OnhGZHvkFHA.3960@TK2MSFTNGP12.phx.gbl...
> Hi vipa,
>
> Try this:
>
> =COUNT($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")
>
> Regards,
> KL
>
>
> "vipa2000" <vipa2000@discussions.microsoft.com> wrote in message
> news:4C1619BC-76CB-4A14-A4A7-D96D725B07A9@microsoft.com...
>>I am trying to setup a spreadsheet where data will be imported in from
>> another package into a sheet within the spreadsheet on a monthly basis.
>> On
>> another sheet I am trying to setup various formula that will analyse this
>> data automatically. Normally i would use Access but other people within
>> the
>> company do not understand Access. The issue:-
>>
>> I can't get the count function or any derivative to do what i want.
>> Example
>> of problem below.
>> A b c d e
>> 120873 50183368 6 blank REL NMAT PRC SETC
>> 120873 50183299 4 blank CLSD PRT NMAT PRC
>> 122873 50185108 3 blank PCNF NMAT PRC SETC
>> 122873 50178522 3 HUDS002277 TECO PCNF PRT NMAT
>> 122873 50147658 5 HUDS003115 REL NMAT PRC SETC
>>
>> I want to total the no of records in column A but deduct those records in
>> column d where a record is found with a hud prefix
>>
>> --
>> Regards vipa
>
>

Seeing Chuck's suggestion made me realise that I had misread the request:

=SUMPRODUCT(--(A1:A5<>""),--(LEFT(D1:D5,3)<>"HUD"))

--
HTH

Sandy
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk


"Sandy Mann" <sandymann2@mailinator.com> wrote in message
news:Oef0nMvkFHA.3200@TK2MSFTNGP10.phx.gbl...
> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
> news:OnhGZHvkFHA.3960@TK2MSFTNGP12.phx.gbl...
>> Hi vipa,
>>
>> Try this:
>>
>> =COUNT($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")
>
> Assumes that every entry in column D with "Hud*" has a corresponding entry
> in Column A which may or may not be true. I would suggest:
>
> =SUMPRODUCT(--(A1:A5<>""),--(LEFT(D1:D5,3)="HUD"))
> --
> HTH
>
> Sandy
> sandymann@mailinator.com
> Replace@mailinator with @tiscali.co.uk
>
>
>

Thanks KL. worked fine.
--
Regards vipa


"KL" wrote:

> sorry, actually meant this:
>
> =COUNTA($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")
>
> KL
>
>
>
> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
> news:OnhGZHvkFHA.3960@TK2MSFTNGP12.phx.gbl...
> > Hi vipa,
> >
> > Try this:
> >
> > =COUNT($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")
> >
> > Regards,
> > KL
> >
> >
> > "vipa2000" <vipa2000@discussions.microsoft.com> wrote in message
> > news:4C1619BC-76CB-4A14-A4A7-D96D725B07A9@microsoft.com...
> >>I am trying to setup a spreadsheet where data will be imported in from
> >> another package into a sheet within the spreadsheet on a monthly basis.
> >> On
> >> another sheet I am trying to setup various formula that will analyse this
> >> data automatically. Normally i would use Access but other people within
> >> the
> >> company do not understand Access. The issue:-
> >>
> >> I can't get the count function or any derivative to do what i want.
> >> Example
> >> of problem below.
> >> A b c d e
> >> 120873 50183368 6 blank REL NMAT PRC SETC
> >> 120873 50183299 4 blank CLSD PRT NMAT PRC
> >> 122873 50185108 3 blank PCNF NMAT PRC SETC
> >> 122873 50178522 3 HUDS002277 TECO PCNF PRT NMAT
> >> 122873 50147658 5 HUDS003115 REL NMAT PRC SETC
> >>
> >> I want to total the no of records in column A but deduct those records in
> >> column d where a record is found with a hud prefix
> >>
> >> --
> >> Regards vipa
> >
> >
>
>
>

Hi vipa,

Try this:

=COUNT($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")

Regards,
KL


"vipa2000" <vipa2000@discussions.microsoft.com> wrote in message
news:4C1619BC-76CB-4A14-A4A7-D96D725B07A9@microsoft.com...
>I am trying to setup a spreadsheet where data will be imported in from
> another package into a sheet within the spreadsheet on a monthly basis. On
> another sheet I am trying to setup various formula that will analyse this
> data automatically. Normally i would use Access but other people within
> the
> company do not understand Access. The issue:-
>
> I can't get the count function or any derivative to do what i want.
> Example
> of problem below.
> A b c d
> e
> 120873 50183368 6 blank REL NMAT PRC SETC
> 120873 50183299 4 blank CLSD PRT NMAT PRC
> 122873 50185108 3 blank PCNF NMAT PRC SETC
> 122873 50178522 3 HUDS002277 TECO PCNF PRT NMAT
> 122873 50147658 5 HUDS003115 REL NMAT PRC SETC
>
> I want to total the no of records in column A but deduct those records in
> column d where a record is found with a hud prefix
>
> --
> Regards vipa

"KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
news:OnhGZHvkFHA.3960@TK2MSFTNGP12.phx.gbl...
> Hi vipa,
>
> Try this:
>
> =COUNT($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")

Assumes that every entry in column D with "Hud*" has a corresponding entry
in Column A which may or may not be true. I would suggest:

=SUMPRODUCT(--(A1:A5<>""),--(LEFT(D1:D5,3)="HUD"))
--
HTH

Sandy
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk

sorry, actually meant this:

=COUNTA($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")

KL



"KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
news:OnhGZHvkFHA.3960@TK2MSFTNGP12.phx.gbl...
> Hi vipa,
>
> Try this:
>
> =COUNT($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")
>
> Regards,
> KL
>
>
> "vipa2000" <vipa2000@discussions.microsoft.com> wrote in message
> news:4C1619BC-76CB-4A14-A4A7-D96D725B07A9@microsoft.com...
>>I am trying to setup a spreadsheet where data will be imported in from
>> another package into a sheet within the spreadsheet on a monthly basis.
>> On
>> another sheet I am trying to setup various formula that will analyse this
>> data automatically. Normally i would use Access but other people within
>> the
>> company do not understand Access. The issue:-
>>
>> I can't get the count function or any derivative to do what i want.
>> Example
>> of problem below.
>> A b c d e
>> 120873 50183368 6 blank REL NMAT PRC SETC
>> 120873 50183299 4 blank CLSD PRT NMAT PRC
>> 122873 50185108 3 blank PCNF NMAT PRC SETC
>> 122873 50178522 3 HUDS002277 TECO PCNF PRT NMAT
>> 122873 50147658 5 HUDS003115 REL NMAT PRC SETC
>>
>> I want to total the no of records in column A but deduct those records in
>> column d where a record is found with a hud prefix
>>
>> --
>> Regards vipa
>
>

One way would be to Autofilter > Custom > "does not equal" >
HUD*..........and then use =SUBTOTAL,2,A:A)

Vaya con Dios,
Chuck CABGx3



"vipa2000" <vipa2000@discussions.microsoft.com> wrote in message
news:4C1619BC-76CB-4A14-A4A7-D96D725B07A9@microsoft.com...
> I am trying to setup a spreadsheet where data will be imported in from
> another package into a sheet within the spreadsheet on a monthly basis. On
> another sheet I am trying to setup various formula that will analyse this
> data automatically. Normally i would use Access but other people within
the
> company do not understand Access. The issue:-
>
> I can't get the count function or any derivative to do what i want.
Example
> of problem below.
> A b c d
e
> 120873 50183368 6 blank REL NMAT PRC SETC
> 120873 50183299 4 blank CLSD PRT NMAT PRC
> 122873 50185108 3 blank PCNF NMAT PRC SETC
> 122873 50178522 3 HUDS002277 TECO PCNF PRT NMAT
> 122873 50147658 5 HUDS003115 REL NMAT PRC SETC
>
> I want to total the no of records in column A but deduct those records in
> column d where a record is found with a hud prefix
>
> --
> Regards vipa

Seeing Chuck's suggestion made me realise that I had misread the request:

=SUMPRODUCT(--(A1:A5<>""),--(LEFT(D1:D5,3)<>"HUD"))

--
HTH

Sandy
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk


"Sandy Mann" <sandymann2@mailinator.com> wrote in message
news:Oef0nMvkFHA.3200@TK2MSFTNGP10.phx.gbl...
> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
> news:OnhGZHvkFHA.3960@TK2MSFTNGP12.phx.gbl...
>> Hi vipa,
>>
>> Try this:
>>
>> =COUNT($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")
>
> Assumes that every entry in column D with "Hud*" has a corresponding entry
> in Column A which may or may not be true. I would suggest:
>
> =SUMPRODUCT(--(A1:A5<>""),--(LEFT(D1:D5,3)="HUD"))
> --
> HTH
>
> Sandy
> sandymann@mailinator.com
> Replace@mailinator with @tiscali.co.uk
>
>
>

Hi vipa,

Try this:

=COUNT($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")

Regards,
KL


"vipa2000" <vipa2000@discussions.microsoft.com> wrote in message
news:4C1619BC-76CB-4A14-A4A7-D96D725B07A9@microsoft.com...
>I am trying to setup a spreadsheet where data will be imported in from
> another package into a sheet within the spreadsheet on a monthly basis. On
> another sheet I am trying to setup various formula that will analyse this
> data automatically. Normally i would use Access but other people within
> the
> company do not understand Access. The issue:-
>
> I can't get the count function or any derivative to do what i want.
> Example
> of problem below.
> A b c d
> e
> 120873 50183368 6 blank REL NMAT PRC SETC
> 120873 50183299 4 blank CLSD PRT NMAT PRC
> 122873 50185108 3 blank PCNF NMAT PRC SETC
> 122873 50178522 3 HUDS002277 TECO PCNF PRT NMAT
> 122873 50147658 5 HUDS003115 REL NMAT PRC SETC
>
> I want to total the no of records in column A but deduct those records in
> column d where a record is found with a hud prefix
>
> --
> Regards vipa

Thanks KL. worked fine.
--
Regards vipa


"KL" wrote:

> sorry, actually meant this:
>
> =COUNTA($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")
>
> KL
>
>
>
> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
> news:OnhGZHvkFHA.3960@TK2MSFTNGP12.phx.gbl...
> > Hi vipa,
> >
> > Try this:
> >
> > =COUNT($A$2:$A$6)-COUNTIF($D$2:$D$6,"HUD*")
> >
> > Regards,
> > KL
> >
> >
> > "vipa2000" <vipa2000@discussions.microsoft.com> wrote in message
> > news:4C1619BC-76CB-4A14-A4A7-D96D725B07A9@microsoft.com...
> >>I am trying to setup a spreadsheet where data will be imported in from
> >> another package into a sheet within the spreadsheet on a monthly basis.
> >> On
> >> another sheet I am trying to setup various formula that will analyse this
> >> data automatically. Normally i would use Access but other people within
> >> the
> >> company do not understand Access. The issue:-
> >>
> >> I can't get the count function or any derivative to do what i want.
> >> Example
> >> of problem below.
> >> A b c d e
> >> 120873 50183368 6 blank REL NMAT PRC SETC
> >> 120873 50183299 4 blank CLSD PRT NMAT PRC
> >> 122873 50185108 3 blank PCNF NMAT PRC SETC
> >> 122873 50178522 3 HUDS002277 TECO PCNF PRT NMAT
> >> 122873 50147658 5 HUDS003115 REL NMAT PRC SETC
> >>
> >> I want to total the no of records in column A but deduct those records in
> >> column d where a record is found with a hud prefix
> >>
> >> --
> >> Regards vipa
> >
> >
>
>
>

I am trying to setup a spreadsheet where data will be imported in from
another package into a sheet within the spreadsheet on a monthly basis. On
another sheet I am trying to setup various formula that will analyse this
data automatically. Normally i would use Access but other people within the
company do not understand Access. The issue:-

I can't get the count function or any derivative to do what i want. Example
of problem below.
A b c d e
120873 50183368 6 blank REL NMAT PRC SETC
120873 50183299 4 blank CLSD PRT NMAT PRC
122873 50185108 3 blank PCNF NMAT PRC SETC
122873 50178522 3 HUDS002277 TECO PCNF PRT NMAT
122873 50147658 5 HUDS003115 REL NMAT PRC SETC

I want to total the no of records in column A but deduct those records in
column d where a record is found with a hud prefix

--
Regards vipa