How to combine an AND and a COUNTIF

[Michael T]

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

 

[Bernard Liengme]

=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)

 

[John Mansfield]

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.

 

[Bernard Liengme]

I think your formula is specific to one set of data. What if the Y was in
another cell?
best wishes

 

[John Mansfield]

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.