I think your formula is specific to one set of data. What if the Y was in
another cell?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"John Mansfield" wrote in message
...
To use your COUNTIF as already set up, you could add a boolean switch (yes
/
no switch) to your present COUNTIF formulas. The switch is whether the
job
has ended i.e. (C9="Y") or (C9="N"). For example, the formula below would
say "if C9 = yes then return the results of the COUNTIF formula".

= (C9="Y") * COUNTIF('Worksheet 1'!$C$2:$F$2,"="&'Worksheet 1'!A2)

If cell C9 is "Y", the COUNTIF results will be returned. If it is "N",
then
zero is returned.

--
John Mansfield
http://www.cellmatrix.net


"Michael T" wrote:

I have a spreadsheet as described below, the numbers in Worksheet 1 are
the
number of job sessions in the week.

I am presently creating data on Worksheet 2 to populate a graph of the
numbers of jobs which have had 1, 2, 3 etc sessions. I used COUNTIF and
it
was fine and the graph was fine.

I now only want to count the numbers of sessions where the job has ended
so
I want to COUNTIF as already set up AND where the Job Ended is "Y".
I have tried a variety of codings but cannot seem to get it right.

Does anybody have any ideas.

Many thanks

Michael.

Worksheet 1

A B C D E
1 Date Job01 Job02 Job03 Job04.....
2 Week01 1 1 0 0
3 Week02 2 1 1 0
4 Week03 1 0 1 1
5 Totals 4 2 2 1
....
9 Job Ended N Y Y Y

Worksheet 2
A B
1 1 =COUNTIF('Worksheet 1'!$B$5:$E$5,"="&A1) = Produces 1
WITH THE AND JOB ENDED I WANT = 1
2 2 =COUNTIF('Worksheet 1'!$B$5:$E$5,"="&A2) = Produces 2
WITH THE AND JOB ENDED I WANT = 2
3 3 =COUNTIF('Worksheet 1'!$B$5:$E$5,"="&A3) = Produces 0
WITH THE AND JOB ENDED I WANT = 0
4 4 =COUNTIF('Worksheet 1'!$B$5:$E$5,"="&A4) = Produces 1
WITH THE AND JOB ENDED I WANT = 0
5 5 =COUNTIF('Worksheet 1'!$B$5:$E$5,"="&A5) = Produces 0
WITH THE AND JOB ENDED I WANT = 0
....