How to combine an AND and a COUNTIF
 

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

How to combine an AND and a COUNTIF
 

=SUMPRODUCT(--('Worksheet 1'!$B$5:$E$5=A1),--('Worksheet 1'!$B$9:$E$9="Y")

For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html

If you have XL2007, look in Help to learn about COUNTIFS (multiple criteria)
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

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

How to combine an AND and a COUNTIF
 

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

How to combine an AND and a COUNTIF
 

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

How to combine an AND and a COUNTIF
 

Hi Bernard,

You're correct, I missed that.

I think this would work entered as an array:

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

I was trying to come up with something that included COUNTIF because the
question stated "I want to COUNTIF as already set up AND where the Job Ended
is Y". However, the SUMPRODUCT function would be better in this case (as you
suggested) because you don't have to enter it as an array and it's easier to
understand.

Thanks again.

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


"Bernard Liengme" wrote:

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