counting based ona number of variables.

[Guest]

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,"HUD*")
=SUMPRODUCT(--(Sheet1!C1:C30000=1),--(Sheet1!D130000=""))

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.

 

[Bob Phillips]

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)

 

[Guest]

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.

 

[Guest]

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!D130000=""))

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.

 

[Bob Phillips]

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)

 

[Guest]

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!D130000=""))

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

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

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?????

 

[Guest]

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

 

[Bob Phillips]

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)