Count if question

[Morfi]

I am trying to create a formula that will count all of the "yes" entries in one column and from that total will subtract any that come under the heading NFA in another column i.e.

The worksheet is called Apr-Jun

Column S - entry will be Yes or No
Column J - entry can be a number of options one of which is NFA

I want a formula on a different worksheet that will count all of the Yes entries in column S but won't include any that are noted as NFA in column J. To count the yes entries at the moment I have the following formula:

=COUNTIF('Apr-Jun'!S2:S16, "Y")

Is there something I can add to this or would I need an entire new formula? If so, ant suggestions ars to what it should be?

 

[VoG]

Try

=SUMPRODUCT(--('Apr-Jun'!S2:S16="Y"),--('Apr-Jun'!J2:J16<>"NFA"))

 

[Morfi]

That works thanks.

I also need to count the number of entries that are over 40 and discount any that are NFAs. I tried using the same formula but ir doesn't seem to work.

Column J - entry can be a number of options one of which is NFA
Column R = Number of days

The formula I have tried is:

=SUMPRODUCT(--('Apr-Jun'!R2:R16=">40"),--('Apr-Jun'!J2:J16<>"NFA"))

Is it something to do with the >40 part that stops it from working?

 

[VoG]

Try

=SUMPRODUCT(--('Apr-Jun'!R2:R16>40),--('Apr-Jun'!J2:J16<>"NFA"))

 

[Morfi]

Thanks but that doesn't seem to work. Is there any other way to do it?

 

[VoG]

In what way doesn't work?

 

[Morfi]

The entries in column R are:

43
5
-2
-4
-4
-5
-19
-15

I want the formula to return the answer of 1 as only 1 is greater than 40 but it's coming back with 5.

 

[VoG]

I get 1

Excel Workbook
	Q	R
2	1	43
3		5
4		-2
5		-4
6		-4
7		-5
8		-19
9		-15
Apr-Jun