Priority col 4 Bas. start date Basic fin. date
120873 50098553 2 23/01/2004 26/01/2004
120873 50086461 6 01/12/2004 31/12/2004
120873 50166711 3 16/02/2005 22/02/2005
120873 50153765 3 HUDS 01/03/2005 01/03/2005
120873 50170114 1 07/06/2005 09/03/2005
120873 50172335 1 22/06/2005 23/03/2005

Right, thanks for everbodies help so far, but this one has me really stiched
up.

=COUNT(Sheet1!A:A,"12*")-COUNTIF(Sheet1!D:D,"HUD*")
=SUMPRODUCT(--(Sheet1!C1:C30000=1),--(Sheet1!D1:D30000=""))

i have used both the above bits of code plus others to interrogate the data
to produce a standard report. Data is to be imported into a spreadsheet
monthly and then analysed on a separate worksheet. I now need to focus on
counting the priority (say 3) with a corrsponding null in col 4 but based
only on the month I want to interrogate, say june. For the above data i
should get 2 events. I don't want the dates to be hardcoded as the data will
change. I had wondered about a drop down list with 1-12 corresponding to the
months and a year one and then the code above could be tweaked to look at the
month/year selected by the user and then 'filter' my data accordingly.

i'll be impressed if this gets cracked.
--
Regards vipa

The explanation makes me ask what is the question? What are you trying to
do, conditional counting, or conditional filtering?

--

HTH

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


"vipa2000" wrote in message
...
Priority col 4 Bas. start date Basic fin. date
120873 50098553 2 23/01/2004 26/01/2004
120873 50086461 6 01/12/2004 31/12/2004
120873 50166711 3 16/02/2005 22/02/2005
120873 50153765 3 HUDS 01/03/2005 01/03/2005
120873 50170114 1 07/06/2005 09/03/2005
120873 50172335 1 22/06/2005 23/03/2005

Right, thanks for everbodies help so far, but this one has me really
stiched
up.

=COUNT(Sheet1!A:A,"12*")-COUNTIF(Sheet1!D:D,"HUD*")
=SUMPRODUCT(--(Sheet1!C1:C30000=1),--(Sheet1!D1:D30000=""))

i have used both the above bits of code plus others to interrogate the
data
to produce a standard report. Data is to be imported into a spreadsheet
monthly and then analysed on a separate worksheet. I now need to focus on
counting the priority (say 3) with a corrsponding null in col 4 but based
only on the month I want to interrogate, say june. For the above data i
should get 2 events. I don't want the dates to be hardcoded as the data
will
change. I had wondered about a drop down list with 1-12 corresponding to
the
months and a year one and then the code above could be tweaked to look at
the
month/year selected by the user and then 'filter' my data accordingly.

i'll be impressed if this gets cracked.
--
Regards vipa

Conditional counting. I just want the count of the priority field for a
particular value, when column 4 is null and the basic start date is looking
at a particular month and year.
--
Regards vipa


"Bob Phillips" wrote:

The explanation makes me ask what is the question? What are you trying to
do, conditional counting, or conditional filtering?

--

HTH

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


"vipa2000" wrote in message
...
Priority col 4 Bas. start date Basic fin. date
120873 50098553 2 23/01/2004 26/01/2004
120873 50086461 6 01/12/2004 31/12/2004
120873 50166711 3 16/02/2005 22/02/2005
120873 50153765 3 HUDS 01/03/2005 01/03/2005
120873 50170114 1 07/06/2005 09/03/2005
120873 50172335 1 22/06/2005 23/03/2005

Right, thanks for everbodies help so far, but this one has me really
stiched
up.

=COUNT(Sheet1!A:A,"12*")-COUNTIF(Sheet1!D:D,"HUD*")
=SUMPRODUCT(--(Sheet1!C1:C30000=1),--(Sheet1!D1:D30000=""))

i have used both the above bits of code plus others to interrogate the
data
to produce a standard report. Data is to be imported into a spreadsheet
monthly and then analysed on a separate worksheet. I now need to focus on
counting the priority (say 3) with a corrsponding null in col 4 but based
only on the month I want to interrogate, say june. For the above data i
should get 2 events. I don't want the dates to be hardcoded as the data
will
change. I had wondered about a drop down list with 1-12 corresponding to
the
months and a year one and then the code above could be tweaked to look at
the
month/year selected by the user and then 'filter' my data accordingly.

i'll be impressed if this gets cracked.
--
Regards vipa

Priority 0 1 2
1 60 12

The above is an extract of the report i want to generate. For example at the
moment behind the number 60 is the code below.

=SUMPRODUCT(--(Sheet1!C1:C30000=1),--(Sheet1!D1:D30000=""))

I want to adapt it so that it will only select those records for a
particular month and year based on the basic start field.( see earlier in
thread). The month and year will will change so as stated I thought of using
a list. My priority fields range from 0-6, but these are fixed.

--
Regards vipa


"vipa2000" wrote:

Conditional counting. I just want the count of the priority field for a
particular value, when column 4 is null and the basic start date is looking
at a particular month and year.
--
Regards vipa


"Bob Phillips" wrote:

The explanation makes me ask what is the question? What are you trying to
do, conditional counting, or conditional filtering?

--

HTH

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


"vipa2000" wrote in message
...
Priority col 4 Bas. start date Basic fin. date
120873 50098553 2 23/01/2004 26/01/2004
120873 50086461 6 01/12/2004 31/12/2004
120873 50166711 3 16/02/2005 22/02/2005
120873 50153765 3 HUDS 01/03/2005 01/03/2005
120873 50170114 1 07/06/2005 09/03/2005
120873 50172335 1 22/06/2005 23/03/2005

Right, thanks for everbodies help so far, but this one has me really
stiched
up.

=COUNT(Sheet1!A:A,"12*")-COUNTIF(Sheet1!D:D,"HUD*")
=SUMPRODUCT(--(Sheet1!C1:C30000=1),--(Sheet1!D1:D30000=""))

i have used both the above bits of code plus others to interrogate the
data
to produce a standard report. Data is to be imported into a spreadsheet
monthly and then analysed on a separate worksheet. I now need to focus on
counting the priority (say 3) with a corrsponding null in col 4 but based
only on the month I want to interrogate, say june. For the above data i
should get 2 events. I don't want the dates to be hardcoded as the data
will
change. I had wondered about a drop down list with 1-12 corresponding to
the
months and a year one and then the code above could be tweaked to look at
the
month/year selected by the user and then 'filter' my data accordingly.

i'll be impressed if this gets cracked.
--
Regards vipa

You have lost me completely.

Your sample data has priorities of 120873, but talk about 6?
Does that formula work, but you just want to extend it to test a date? If
so, what column is the date in, and what do you want to test it for?

--

HTH

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


"vipa2000" wrote in message
...
Priority 0 1 2
1 60 12

The above is an extract of the report i want to generate. For example at
the
moment behind the number 60 is the code below.

=SUMPRODUCT(--(Sheet1!C1:C30000=1),--(Sheet1!D1:D30000=""))

I want to adapt it so that it will only select those records for a
particular month and year based on the basic start field.( see earlier in
thread). The month and year will will change so as stated I thought of
using
a list. My priority fields range from 0-6, but these are fixed.

--
Regards vipa


"vipa2000" wrote:

Conditional counting. I just want the count of the priority field for a
particular value, when column 4 is null and the basic start date is
looking
at a particular month and year.
--
Regards vipa


"Bob Phillips" wrote:

The explanation makes me ask what is the question? What are you trying
to
do, conditional counting, or conditional filtering?

--

HTH

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


"vipa2000" wrote in message
...
Priority col 4 Bas. start date Basic fin. date
120873 50098553 2 23/01/2004 26/01/2004
120873 50086461 6 01/12/2004 31/12/2004
120873 50166711 3 16/02/2005 22/02/2005
120873 50153765 3 HUDS 01/03/2005 01/03/2005
120873 50170114 1 07/06/2005 09/03/2005
120873 50172335 1 22/06/2005 23/03/2005

Right, thanks for everbodies help so far, but this one has me really
stiched
up.

=COUNT(Sheet1!A:A,"12*")-COUNTIF(Sheet1!D:D,"HUD*")
=SUMPRODUCT(--(Sheet1!C1:C30000=1),--(Sheet1!D1:D30000=""))

i have used both the above bits of code plus others to interrogate
the
data
to produce a standard report. Data is to be imported into a
spreadsheet
monthly and then analysed on a separate worksheet. I now need to
focus on
counting the priority (say 3) with a corrsponding null in col 4 but
based
only on the month I want to interrogate, say june. For the above
data i
should get 2 events. I don't want the dates to be hardcoded as the
data
will
change. I had wondered about a drop down list with 1-12
corresponding to
the
months and a year one and then the code above could be tweaked to
look at
the
month/year selected by the user and then 'filter' my data
accordingly.

i'll be impressed if this gets cracked.
--
Regards vipa

Sorry Bob I have noticed that the columns headings have got corrupted when
uploaded.

1, 2, 3, 4, 5,
6,
Cost C, Order, Pri, plan, start date,
fin. date
122873, 50076916, 1, blank, 29/09/2003,
30/09/2003
120873, 50095000, 3, hud, 08/01/2004,
09/01/2004
120873, 50172335, 2, blank, 22/03/2005,
23/03/2005
120873, 50174753, 1, blank, 07/06/2005,
08/04/2005

hopefully the above will appear correctly.
column headings are 1,2,3,4,5,6 under those should be Cost
C,Order,Pri,plan,start date,fin. date. respectfully and then under those the
actual data. I have separated out using commas so hopefully you can decipher
if corrupt.

the above in my spreasheet appears as a mass of data of a worksheet. On a
separate sheet i then run formula to interrogate
the data. Users will paste over the 'data' spreadsheet monthly leaving the
formula spreadsheet intact. The formula spreadsheet functions as a report.
the code works bob that you helped me out with

=SUMPRODUCT(--(Sheet1!C1:C30000=1),--(Sheet1!D1:D30000=""))

on the above it would find 2 records in column c with corresponding blank
entries in column d. I need to adapt or create formula that will look for
priority 1's lets say, with a blank entry in column d, but limited to a
particular month and year based on column 5.

On my report sheet i do a totals overview of all priorities raised ina
particular month.

ie.
June report
Priotity 1 - 30 Tasks raised
Priotity 2 - 16 Tasks raised


--
Regards vipa


"Bob Phillips" wrote:

You have lost me completely.

Your sample data has priorities of 120873, but talk about 6?
Does that formula work, but you just want to extend it to test a date? If
so, what column is the date in, and what do you want to test it for?

--

HTH

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


"vipa2000" wrote in message
...
Priority 0 1 2
1 60 12

The above is an extract of the report i want to generate. For example at
the
moment behind the number 60 is the code below.

=SUMPRODUCT(--(Sheet1!C1:C30000=1),--(Sheet1!D1:D30000=""))

I want to adapt it so that it will only select those records for a
particular month and year based on the basic start field.( see earlier in
thread). The month and year will will change so as stated I thought of
using
a list. My priority fields range from 0-6, but these are fixed.

--
Regards vipa


"vipa2000" wrote:

Conditional counting. I just want the count of the priority field for a
particular value, when column 4 is null and the basic start date is
looking
at a particular month and year.
--
Regards vipa


"Bob Phillips" wrote:

The explanation makes me ask what is the question? What are you trying
to
do, conditional counting, or conditional filtering?

--

HTH

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


"vipa2000" wrote in message
...
Priority col 4 Bas. start date Basic fin. date
120873 50098553 2 23/01/2004 26/01/2004
120873 50086461 6 01/12/2004 31/12/2004
120873 50166711 3 16/02/2005 22/02/2005
120873 50153765 3 HUDS 01/03/2005 01/03/2005
120873 50170114 1 07/06/2005 09/03/2005
120873 50172335 1 22/06/2005 23/03/2005

Right, thanks for everbodies help so far, but this one has me really
stiched
up.

=COUNT(Sheet1!A:A,"12*")-COUNTIF(Sheet1!D:D,"HUD*")
=SUMPRODUCT(--(Sheet1!C1:C30000=1),--(Sheet1!D1:D30000=""))

i have used both the above bits of code plus others to interrogate
the
data
to produce a standard report. Data is to be imported into a
spreadsheet
monthly and then analysed on a separate worksheet. I now need to
focus on
counting the priority (say 3) with a corrsponding null in col 4 but
based
only on the month I want to interrogate, say june. For the above
data i
should get 2 events. I don't want the dates to be hardcoded as the
data
will
change. I had wondered about a drop down list with 1-12
corresponding to
the
months and a year one and then the code above could be tweaked to
look at
the
month/year selected by the user and then 'filter' my data
accordingly.

i'll be impressed if this gets cracked.
--
Regards vipa

Okay, does this do it

=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(
Sheet1!$E$2:$E$3000)=6))

which will check for June.

If you data spans years, you might want to test that also

=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(
Sheet1!$E$2:$E$3000)=6),--(YEAR(Sheet1!$E$2:$E$3000)=2005))

--

HTH

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


"vipa2000" wrote in message
...
Sorry Bob I have noticed that the columns headings have got corrupted when
uploaded.

1, 2, 3, 4, 5,
6,
Cost C, Order, Pri, plan, start date,
fin. date
122873, 50076916, 1, blank, 29/09/2003,
30/09/2003
120873, 50095000, 3, hud, 08/01/2004,
09/01/2004
120873, 50172335, 2, blank, 22/03/2005,
23/03/2005
120873, 50174753, 1, blank, 07/06/2005,
08/04/2005

hopefully the above will appear correctly.
column headings are 1,2,3,4,5,6 under those should be Cost
C,Order,Pri,plan,start date,fin. date. respectfully and then under those
the
actual data. I have separated out using commas so hopefully you can
decipher
if corrupt.

the above in my spreasheet appears as a mass of data of a worksheet. On a
separate sheet i then run formula to interrogate
the data. Users will paste over the 'data' spreadsheet monthly leaving the
formula spreadsheet intact. The formula spreadsheet functions as a report.
the code works bob that you helped me out with

=SUMPRODUCT(--(Sheet1!C1:C30000=1),--(Sheet1!D1:D30000=""))

on the above it would find 2 records in column c with corresponding blank
entries in column d. I need to adapt or create formula that will look for
priority 1's lets say, with a blank entry in column d, but limited to a
particular month and year based on column 5.

On my report sheet i do a totals overview of all priorities raised ina
particular month.

ie.
June report
Priotity 1 - 30 Tasks raised
Priotity 2 - 16 Tasks raised


--
Regards vipa


"Bob Phillips" wrote:

You have lost me completely.

Your sample data has priorities of 120873, but talk about 6?
Does that formula work, but you just want to extend it to test a date?
If
so, what column is the date in, and what do you want to test it for?

--

HTH

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


"vipa2000" wrote in message
...
Priority 0 1 2
1 60 12

The above is an extract of the report i want to generate. For example
at
the
moment behind the number 60 is the code below.

=SUMPRODUCT(--(Sheet1!C1:C30000=1),--(Sheet1!D1:D30000=""))

I want to adapt it so that it will only select those records for a
particular month and year based on the basic start field.( see earlier
in
thread). The month and year will will change so as stated I thought of
using
a list. My priority fields range from 0-6, but these are fixed.

--
Regards vipa


"vipa2000" wrote:

Conditional counting. I just want the count of the priority field
for a
particular value, when column 4 is null and the basic start date is
looking
at a particular month and year.
--
Regards vipa


"Bob Phillips" wrote:

The explanation makes me ask what is the question? What are you
trying
to
do, conditional counting, or conditional filtering?

--

HTH

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


"vipa2000" wrote in message
...
Priority col 4 Bas. start date Basic fin. date
120873 50098553 2 23/01/2004 26/01/2004
120873 50086461 6 01/12/2004 31/12/2004
120873 50166711 3 16/02/2005 22/02/2005
120873 50153765 3 HUDS 01/03/2005 01/03/2005
120873 50170114 1 07/06/2005 09/03/2005
120873 50172335 1 22/06/2005 23/03/2005

Right, thanks for everbodies help so far, but this one has me
really
stiched
up.

=COUNT(Sheet1!A:A,"12*")-COUNTIF(Sheet1!D:D,"HUD*")
=SUMPRODUCT(--(Sheet1!C1:C30000=1),--(Sheet1!D1:D30000=""))

i have used both the above bits of code plus others to
interrogate
the
data
to produce a standard report. Data is to be imported into a
spreadsheet
monthly and then analysed on a separate worksheet. I now need to
focus on
counting the priority (say 3) with a corrsponding null in col 4
but
based
only on the month I want to interrogate, say june. For the above
data i
should get 2 events. I don't want the dates to be hardcoded as
the
data
will
change. I had wondered about a drop down list with 1-12
corresponding to
the
months and a year one and then the code above could be tweaked
to
look at
the
month/year selected by the user and then 'filter' my data
accordingly.

i'll be impressed if this gets cracked.
--
Regards vipa

thanks Bob, $64000 question can you make it so that the variables, month and
year, are based on a cell reference or a pick list. I don't want the users to
have to change the code on a monthly basis
--
Regards vipa


"Bob Phillips" wrote:

Okay, does this do it

=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(
Sheet1!$E$2:$E$3000)=6))

which will check for June.

If you data spans years, you might want to test that also

=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(
Sheet1!$E$2:$E$3000)=6),--(YEAR(Sheet1!$E$2:$E$3000)=2005))

--

HTH

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


"vipa2000" wrote in message
...
Sorry Bob I have noticed that the columns headings have got corrupted when
uploaded.

1, 2, 3, 4, 5,
6,
Cost C, Order, Pri, plan, start date,
fin. date
122873, 50076916, 1, blank, 29/09/2003,
30/09/2003
120873, 50095000, 3, hud, 08/01/2004,
09/01/2004
120873, 50172335, 2, blank, 22/03/2005,
23/03/2005
120873, 50174753, 1, blank, 07/06/2005,
08/04/2005

hopefully the above will appear correctly.
column headings are 1,2,3,4,5,6 under those should be Cost
C,Order,Pri,plan,start date,fin. date. respectfully and then under those
the
actual data. I have separated out using commas so hopefully you can
decipher
if corrupt.

the above in my spreasheet appears as a mass of data of a worksheet. On a
separate sheet i then run formula to interrogate
the data. Users will paste over the 'data' spreadsheet monthly leaving the
formula spreadsheet intact. The formula spreadsheet functions as a report.
the code works bob that you helped me out with

=SUMPRODUCT(--(Sheet1!C1:C30000=1),--(Sheet1!D1:D30000=""))

on the above it would find 2 records in column c with corresponding blank
entries in column d. I need to adapt or create formula that will look for
priority 1's lets say, with a blank entry in column d, but limited to a
particular month and year based on column 5.

On my report sheet i do a totals overview of all priorities raised ina
particular month.

ie.
June report
Priotity 1 - 30 Tasks raised
Priotity 2 - 16 Tasks raised


--
Regards vipa


"Bob Phillips" wrote:

You have lost me completely.

Your sample data has priorities of 120873, but talk about 6?
Does that formula work, but you just want to extend it to test a date?
If
so, what column is the date in, and what do you want to test it for?

--

HTH

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


"vipa2000" wrote in message
...
Priority 0 1 2
1 60 12

The above is an extract of the report i want to generate. For example
at
the
moment behind the number 60 is the code below.

=SUMPRODUCT(--(Sheet1!C1:C30000=1),--(Sheet1!D1:D30000=""))

I want to adapt it so that it will only select those records for a
particular month and year based on the basic start field.( see earlier
in
thread). The month and year will will change so as stated I thought of
using
a list. My priority fields range from 0-6, but these are fixed.

--
Regards vipa


"vipa2000" wrote:

Conditional counting. I just want the count of the priority field
for a
particular value, when column 4 is null and the basic start date is
looking
at a particular month and year.
--
Regards vipa


"Bob Phillips" wrote:

The explanation makes me ask what is the question? What are you
trying
to
do, conditional counting, or conditional filtering?

--

HTH

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


"vipa2000" wrote in message
...
Priority col 4 Bas. start date Basic fin. date
120873 50098553 2 23/01/2004 26/01/2004
120873 50086461 6 01/12/2004 31/12/2004
120873 50166711 3 16/02/2005 22/02/2005
120873 50153765 3 HUDS 01/03/2005 01/03/2005
120873 50170114 1 07/06/2005 09/03/2005
120873 50172335 1 22/06/2005 23/03/2005

Right, thanks for everbodies help so far, but this one has me
really
stiched
up.

=COUNT(Sheet1!A:A,"12*")-COUNTIF(Sheet1!D:D,"HUD*")
=SUMPRODUCT(--(Sheet1!C1:C30000=1),--(Sheet1!D1:D30000=""))

i have used both the above bits of code plus others to
interrogate
the
data
to produce a standard report. Data is to be imported into a
spreadsheet
monthly and then analysed on a separate worksheet. I now need to
focus on
counting the priority (say 3) with a corrsponding null in col 4
but
based
only on the month I want to interrogate, say june. For the above
data i
should get 2 events. I don't want the dates to be hardcoded as
the
data
will
change. I had wondered about a drop down list with 1-12
corresponding to
the
months and a year one and then the code above could be tweaked
to
look at
the
month/year selected by the user and then 'filter' my data
accordingly.

i'll be impressed if this gets cracked.
--
Regards vipa

Of course, assuming N1 for the chosen month, in format January etc., and N2
being year, use

=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(TEXT(S
heet1!$E$2:$E$3000),"mmmm")=N1),--(YEAR(Sheet1!$E$2:$E$3000)=N2))


--

HTH

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


"vipa2000" wrote in message
...
thanks Bob, $64000 question can you make it so that the variables, month
and
year, are based on a cell reference or a pick list. I don't want the users
to
have to change the code on a monthly basis
--
Regards vipa


"Bob Phillips" wrote:

Okay, does this do it


=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(
Sheet1!$E$2:$E$3000)=6))

which will check for June.

If you data spans years, you might want to test that also


=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(
Sheet1!$E$2:$E$3000)=6),--(YEAR(Sheet1!$E$2:$E$3000)=2005))

--

HTH

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


"vipa2000" wrote in message
...
Sorry Bob I have noticed that the columns headings have got corrupted
when
uploaded.

1, 2, 3, 4, 5,
6,
Cost C, Order, Pri, plan, start date,
fin. date
122873, 50076916, 1, blank, 29/09/2003,
30/09/2003
120873, 50095000, 3, hud, 08/01/2004,
09/01/2004
120873, 50172335, 2, blank, 22/03/2005,
23/03/2005
120873, 50174753, 1, blank, 07/06/2005,
08/04/2005

hopefully the above will appear correctly.
column headings are 1,2,3,4,5,6 under those should be Cost
C,Order,Pri,plan,start date,fin. date. respectfully and then under
those
the
actual data. I have separated out using commas so hopefully you can
decipher
if corrupt.

the above in my spreasheet appears as a mass of data of a worksheet.
On a
separate sheet i then run formula to interrogate
the data. Users will paste over the 'data' spreadsheet monthly leaving
the
formula spreadsheet intact. The formula spreadsheet functions as a
report.
the code works bob that you helped me out with

=SUMPRODUCT(--(Sheet1!C1:C30000=1),--(Sheet1!D1:D30000=""))

on the above it would find 2 records in column c with corresponding
blank
entries in column d. I need to adapt or create formula that will look
for
priority 1's lets say, with a blank entry in column d, but limited to
a
particular month and year based on column 5.

On my report sheet i do a totals overview of all priorities raised ina
particular month.

ie.
June report
Priotity 1 - 30 Tasks raised
Priotity 2 - 16 Tasks raised


--
Regards vipa


"Bob Phillips" wrote:

You have lost me completely.

Your sample data has priorities of 120873, but talk about 6?
Does that formula work, but you just want to extend it to test a
date?
If
so, what column is the date in, and what do you want to test it for?

--

HTH

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


"vipa2000" wrote in message
...
Priority 0 1 2
1 60 12

The above is an extract of the report i want to generate. For
example
at
the
moment behind the number 60 is the code below.

=SUMPRODUCT(--(Sheet1!C1:C30000=1),--(Sheet1!D1:D30000=""))

I want to adapt it so that it will only select those records for a
particular month and year based on the basic start field.( see
earlier
in
thread). The month and year will will change so as stated I
thought of
using
a list. My priority fields range from 0-6, but these are fixed.

--
Regards vipa


"vipa2000" wrote:

Conditional counting. I just want the count of the priority
field
for a
particular value, when column 4 is null and the basic start date
is
looking
at a particular month and year.
--
Regards vipa


"Bob Phillips" wrote:

The explanation makes me ask what is the question? What are
you
trying
to
do, conditional counting, or conditional filtering?

--

HTH

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


"vipa2000" wrote in
message
...
Priority col 4 Bas. start date Basic fin. date
120873 50098553 2 23/01/2004 26/01/2004
120873 50086461 6 01/12/2004 31/12/2004
120873 50166711 3 16/02/2005 22/02/2005
120873 50153765 3 HUDS 01/03/2005 01/03/2005
120873 50170114 1 07/06/2005 09/03/2005
120873 50172335 1 22/06/2005 23/03/2005

Right, thanks for everbodies help so far, but this one has
me
really
stiched
up.

=COUNT(Sheet1!A:A,"12*")-COUNTIF(Sheet1!D:D,"HUD*")
=SUMPRODUCT(--(Sheet1!C1:C30000=1),--(Sheet1!D1:D30000=""))

i have used both the above bits of code plus others to
interrogate
the
data
to produce a standard report. Data is to be imported into a
spreadsheet
monthly and then analysed on a separate worksheet. I now
need to
focus on
counting the priority (say 3) with a corrsponding null in
col 4
but
based
only on the month I want to interrogate, say june. For the
above
data i
should get 2 events. I don't want the dates to be hardcoded
as
the
data
will
change. I had wondered about a drop down list with 1-12
corresponding to
the
months and a year one and then the code above could be
tweaked
to
look at
the
month/year selected by the user and then 'filter' my data
accordingly.

i'll be impressed if this gets cracked.
--
Regards vipa

apologies Bob if i am being a bit slow. Are you saying n1 could equal a cell
reference that could contain a no? Or do i need to name a range or?????

--
Regards vipa


"Bob Phillips" wrote:

Of course, assuming N1 for the chosen month, in format January etc., and N2
being year, use

=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(TEXT(S
heet1!$E$2:$E$3000),"mmmm")=N1),--(YEAR(Sheet1!$E$2:$E$3000)=N2))


--

HTH

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


"vipa2000" wrote in message
...
thanks Bob, $64000 question can you make it so that the variables, month
and
year, are based on a cell reference or a pick list. I don't want the users
to
have to change the code on a monthly basis
--
Regards vipa


"Bob Phillips" wrote:

Okay, does this do it


=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(
Sheet1!$E$2:$E$3000)=6))

which will check for June.

If you data spans years, you might want to test that also


=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(
Sheet1!$E$2:$E$3000)=6),--(YEAR(Sheet1!$E$2:$E$3000)=2005))

--

HTH

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


"vipa2000" wrote in message
...
Sorry Bob I have noticed that the columns headings have got corrupted
when
uploaded.

1, 2, 3, 4, 5,
6,
Cost C, Order, Pri, plan, start date,
fin. date
122873, 50076916, 1, blank, 29/09/2003,
30/09/2003
120873, 50095000, 3, hud, 08/01/2004,
09/01/2004
120873, 50172335, 2, blank, 22/03/2005,
23/03/2005
120873, 50174753, 1, blank, 07/06/2005,
08/04/2005

hopefully the above will appear correctly.
column headings are 1,2,3,4,5,6 under those should be Cost
C,Order,Pri,plan,start date,fin. date. respectfully and then under
those
the
actual data. I have separated out using commas so hopefully you can
decipher
if corrupt.

the above in my spreasheet appears as a mass of data of a worksheet.
On a
separate sheet i then run formula to interrogate
the data. Users will paste over the 'data' spreadsheet monthly leaving
the
formula spreadsheet intact. The formula spreadsheet functions as a
report.
the code works bob that you helped me out with

=SUMPRODUCT(--(Sheet1!C1:C30000=1),--(Sheet1!D1:D30000=""))

on the above it would find 2 records in column c with corresponding
blank
entries in column d. I need to adapt or create formula that will look
for
priority 1's lets say, with a blank entry in column d, but limited to
a
particular month and year based on column 5.

On my report sheet i do a totals overview of all priorities raised ina
particular month.

ie.
June report
Priotity 1 - 30 Tasks raised
Priotity 2 - 16 Tasks raised


--
Regards vipa


"Bob Phillips" wrote:

You have lost me completely.

Your sample data has priorities of 120873, but talk about 6?
Does that formula work, but you just want to extend it to test a
date?
If
so, what column is the date in, and what do you want to test it for?

--

HTH

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


"vipa2000" wrote in message
...
Priority 0 1 2
1 60 12

The above is an extract of the report i want to generate. For
example
at
the
moment behind the number 60 is the code below.

=SUMPRODUCT(--(Sheet1!C1:C30000=1),--(Sheet1!D1:D30000=""))

I want to adapt it so that it will only select those records for a
particular month and year based on the basic start field.( see
earlier
in
thread). The month and year will will change so as stated I
thought of
using
a list. My priority fields range from 0-6, but these are fixed.

--
Regards vipa


"vipa2000" wrote:

Conditional counting. I just want the count of the priority
field
for a
particular value, when column 4 is null and the basic start date
is
looking
at a particular month and year.
--
Regards vipa


"Bob Phillips" wrote:

The explanation makes me ask what is the question? What are
you
trying
to
do, conditional counting, or conditional filtering?

--

HTH

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


"vipa2000" wrote in
message
...
Priority col 4 Bas. start date Basic fin. date
120873 50098553 2 23/01/2004 26/01/2004
120873 50086461 6 01/12/2004 31/12/2004
120873 50166711 3 16/02/2005 22/02/2005
120873 50153765 3 HUDS 01/03/2005 01/03/2005
120873 50170114 1 07/06/2005 09/03/2005
120873 50172335 1 22/06/2005 23/03/2005

Right, thanks for everbodies help so far, but this one has
me
really
stiched
up.

=COUNT(Sheet1!A:A,"12*")-COUNTIF(Sheet1!D:D,"HUD*")
=SUMPRODUCT(--(Sheet1!C1:C30000=1),--(Sheet1!D1:D30000=""))

i have used both the above bits of code plus others to
interrogate
the
data
to produce a standard report. Data is to be imported into a
spreadsheet
monthly and then analysed on a separate worksheet. I now
need to
focus on
counting the priority (say 3) with a corrsponding null in
col 4
but
based
only on the month I want to interrogate, say june. For the
above
data i
should get 2 events. I don't want the dates to be hardcoded
as
the
data
will
change. I had wondered about a drop down list with 1-12
corresponding to
the
months and a year one and then the code above could be
tweaked
to
look at
the
month/year selected by the user and then 'filter' my data
accordingly.

i'll be impressed if this gets cracked.
--
Regards vipa

cracked it Bob. Cracking bit of code, thanks once again. Dare i say it, i'll
be back!
--
Regards vipa


"vipa2000" wrote:

apologies Bob if i am being a bit slow. Are you saying n1 could equal a cell
reference that could contain a no? Or do i need to name a range or?????

--
Regards vipa


"Bob Phillips" wrote:

Of course, assuming N1 for the chosen month, in format January etc., and N2
being year, use

=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(TEXT(S
heet1!$E$2:$E$3000),"mmmm")=N1),--(YEAR(Sheet1!$E$2:$E$3000)=N2))


--

HTH

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


"vipa2000" wrote in message
...
thanks Bob, $64000 question can you make it so that the variables, month
and
year, are based on a cell reference or a pick list. I don't want the users
to
have to change the code on a monthly basis
--
Regards vipa


"Bob Phillips" wrote:

Okay, does this do it


=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(
Sheet1!$E$2:$E$3000)=6))

which will check for June.

If you data spans years, you might want to test that also


=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(
Sheet1!$E$2:$E$3000)=6),--(YEAR(Sheet1!$E$2:$E$3000)=2005))

--

HTH

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


"vipa2000" wrote in message
...
Sorry Bob I have noticed that the columns headings have got corrupted
when
uploaded.

1, 2, 3, 4, 5,
6,
Cost C, Order, Pri, plan, start date,
fin. date
122873, 50076916, 1, blank, 29/09/2003,
30/09/2003
120873, 50095000, 3, hud, 08/01/2004,
09/01/2004
120873, 50172335, 2, blank, 22/03/2005,
23/03/2005
120873, 50174753, 1, blank, 07/06/2005,
08/04/2005

hopefully the above will appear correctly.
column headings are 1,2,3,4,5,6 under those should be Cost
C,Order,Pri,plan,start date,fin. date. respectfully and then under
those
the
actual data. I have separated out using commas so hopefully you can
decipher
if corrupt.

the above in my spreasheet appears as a mass of data of a worksheet.
On a
separate sheet i then run formula to interrogate
the data. Users will paste over the 'data' spreadsheet monthly leaving
the
formula spreadsheet intact. The formula spreadsheet functions as a
report.
the code works bob that you helped me out with

=SUMPRODUCT(--(Sheet1!C1:C30000=1),--(Sheet1!D1:D30000=""))

on the above it would find 2 records in column c with corresponding
blank
entries in column d. I need to adapt or create formula that will look
for
priority 1's lets say, with a blank entry in column d, but limited to
a
particular month and year based on column 5.

On my report sheet i do a totals overview of all priorities raised ina
particular month.

ie.
June report
Priotity 1 - 30 Tasks raised
Priotity 2 - 16 Tasks raised


--
Regards vipa


"Bob Phillips" wrote:

You have lost me completely.

Your sample data has priorities of 120873, but talk about 6?
Does that formula work, but you just want to extend it to test a
date?
If
so, what column is the date in, and what do you want to test it for?

--

HTH

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


"vipa2000" wrote in message
...
Priority 0 1 2
1 60 12

The above is an extract of the report i want to generate. For
example
at
the
moment behind the number 60 is the code below.

=SUMPRODUCT(--(Sheet1!C1:C30000=1),--(Sheet1!D1:D30000=""))

I want to adapt it so that it will only select those records for a
particular month and year based on the basic start field.( see
earlier
in
thread). The month and year will will change so as stated I
thought of
using
a list. My priority fields range from 0-6, but these are fixed.

--
Regards vipa


"vipa2000" wrote:

Conditional counting. I just want the count of the priority
field
for a
particular value, when column 4 is null and the basic start date
is
looking
at a particular month and year.
--
Regards vipa


"Bob Phillips" wrote:

The explanation makes me ask what is the question? What are
you
trying
to
do, conditional counting, or conditional filtering?

--

HTH

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


"vipa2000" wrote in
message
...
Priority col 4 Bas. start date Basic fin. date
120873 50098553 2 23/01/2004 26/01/2004
120873 50086461 6 01/12/2004 31/12/2004
120873 50166711 3 16/02/2005 22/02/2005
120873 50153765 3 HUDS 01/03/2005 01/03/2005
120873 50170114 1 07/06/2005 09/03/2005
120873 50172335 1 22/06/2005 23/03/2005

Right, thanks for everbodies help so far, but this one has
me
really
stiched
up.

=COUNT(Sheet1!A:A,"12*")-COUNTIF(Sheet1!D:D,"HUD*")
=SUMPRODUCT(--(Sheet1!C1:C30000=1),--(Sheet1!D1:D30000=""))

i have used both the above bits of code plus others to
interrogate
the
data
to produce a standard report. Data is to be imported into a
spreadsheet
monthly and then analysed on a separate worksheet. I now
need to
focus on
counting the priority (say 3) with a corrsponding null in
col 4
but
based
only on the month I want to interrogate, say june. For the
above
data i
should get 2 events. I don't want the dates to be hardcoded
as
the
data
will
change. I had wondered about a drop down list with 1-12
corresponding to
the
months and a year one and then the code above could be
tweaked
to
look at
the
month/year selected by the user and then 'filter' my data
accordingly.

i'll be impressed if this gets cracked.
--
Regards vipa

No I am saying that N1 would be a cell where the month name is typed in,
such as January, or you have a data validation list and the user has picked
say June

--

HTH

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


"vipa2000" wrote in message
...
apologies Bob if i am being a bit slow. Are you saying n1 could equal a
cell
reference that could contain a no? Or do i need to name a range or?????

--
Regards vipa


"Bob Phillips" wrote:

Of course, assuming N1 for the chosen month, in format January etc., and
N2
being year, use


=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(TEXT(S
heet1!$E$2:$E$3000),"mmmm")=N1),--(YEAR(Sheet1!$E$2:$E$3000)=N2))


--

HTH

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


"vipa2000" wrote in message
...
thanks Bob, $64000 question can you make it so that the variables,
month
and
year, are based on a cell reference or a pick list. I don't want the
users
to
have to change the code on a monthly basis
--
Regards vipa


"Bob Phillips" wrote:

Okay, does this do it



=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(
Sheet1!$E$2:$E$3000)=6))

which will check for June.

If you data spans years, you might want to test that also



=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(
Sheet1!$E$2:$E$3000)=6),--(YEAR(Sheet1!$E$2:$E$3000)=2005))

--

HTH

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


"vipa2000" wrote in message
...
Sorry Bob I have noticed that the columns headings have got
corrupted
when
uploaded.

1, 2, 3, 4, 5,
6,
Cost C, Order, Pri, plan, start date,
fin. date
122873, 50076916, 1, blank, 29/09/2003,
30/09/2003
120873, 50095000, 3, hud, 08/01/2004,
09/01/2004
120873, 50172335, 2, blank, 22/03/2005,
23/03/2005
120873, 50174753, 1, blank, 07/06/2005,
08/04/2005

hopefully the above will appear correctly.
column headings are 1,2,3,4,5,6 under those should be Cost
C,Order,Pri,plan,start date,fin. date. respectfully and then under
those
the
actual data. I have separated out using commas so hopefully you
can
decipher
if corrupt.

the above in my spreasheet appears as a mass of data of a
worksheet.
On a
separate sheet i then run formula to interrogate
the data. Users will paste over the 'data' spreadsheet monthly
leaving
the
formula spreadsheet intact. The formula spreadsheet functions as a
report.
the code works bob that you helped me out with

=SUMPRODUCT(--(Sheet1!C1:C30000=1),--(Sheet1!D1:D30000=""))

on the above it would find 2 records in column c with
corresponding
blank
entries in column d. I need to adapt or create formula that will
look
for
priority 1's lets say, with a blank entry in column d, but limited
to
a
particular month and year based on column 5.

On my report sheet i do a totals overview of all priorities raised
ina
particular month.

ie.
June report
Priotity 1 - 30 Tasks raised
Priotity 2 - 16 Tasks raised


--
Regards vipa


"Bob Phillips" wrote:

You have lost me completely.

Your sample data has priorities of 120873, but talk about 6?
Does that formula work, but you just want to extend it to test a
date?
If
so, what column is the date in, and what do you want to test it
for?

--

HTH

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


"vipa2000" wrote in message
...
Priority 0 1 2
1 60 12

The above is an extract of the report i want to generate. For
example
at
the
moment behind the number 60 is the code below.

=SUMPRODUCT(--(Sheet1!C1:C30000=1),--(Sheet1!D1:D30000=""))

I want to adapt it so that it will only select those records
for a
particular month and year based on the basic start field.( see
earlier
in
thread). The month and year will will change so as stated I
thought of
using
a list. My priority fields range from 0-6, but these are
fixed.

--
Regards vipa


"vipa2000" wrote:

Conditional counting. I just want the count of the priority
field
for a
particular value, when column 4 is null and the basic start
date
is
looking
at a particular month and year.
--
Regards vipa


"Bob Phillips" wrote:

The explanation makes me ask what is the question? What
are
you
trying
to
do, conditional counting, or conditional filtering?

--

HTH

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


"vipa2000" wrote in
message
...
Priority col 4 Bas. start date Basic fin. date
120873 50098553 2 23/01/2004 26/01/2004
120873 50086461 6 01/12/2004 31/12/2004
120873 50166711 3 16/02/2005 22/02/2005
120873 50153765 3 HUDS 01/03/2005 01/03/2005
120873 50170114 1 07/06/2005 09/03/2005
120873 50172335 1 22/06/2005 23/03/2005

Right, thanks for everbodies help so far, but this one
has
me
really
stiched
up.

=COUNT(Sheet1!A:A,"12*")-COUNTIF(Sheet1!D:D,"HUD*")

=SUMPRODUCT(--(Sheet1!C1:C30000=1),--(Sheet1!D1:D30000=""))

i have used both the above bits of code plus others to
interrogate
the
data
to produce a standard report. Data is to be imported
into a
spreadsheet
monthly and then analysed on a separate worksheet. I now
need to
focus on
counting the priority (say 3) with a corrsponding null
in
col 4
but
based
only on the month I want to interrogate, say june. For
the
above
data i
should get 2 events. I don't want the dates to be
hardcoded
as
the
data
will
change. I had wondered about a drop down list with 1-12
corresponding to
the
months and a year one and then the code above could be
tweaked
to
look at
the
month/year selected by the user and then 'filter' my
data
accordingly.

i'll be impressed if this gets cracked.
--
Regards vipa