count problem
 

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" wrote in message
...
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" wrote in message
...
Hi vipa,

Try this:

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

Regards,
KL


"vipa2000" wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
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

Replace@mailinator with @tiscali.co.uk


"Sandy Mann" wrote in message
...
"KL" wrote in message
...
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

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" wrote in message
...
Hi vipa,

Try this:

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

Regards,
KL


"vipa2000" wrote in message
...
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