COUNTIFS RETURNING WRONG VALUE

Question

Hi,

          I wonder if someone is able to help? I am trying to evaluate supplier performance by measuring results of certain products against spec values. I have a very large table of data in a separate worksheet and a summary table (below) in a different worksheet. I am using a COUNTIFS formula to count the number of results which meet spec for each supplier / product. The formula works fine when there is a value in the spec column but when I put N/A (i.e. the spec doesn't apply) the formula returns an incorrect value (it should be 0 or error).

The formula I am using is: = COUNTIFS(Table1_[Results],"<="&D2,Table1[Supplier],$A2,Table1[Product & Description],$B2)

SUPPLIER	PRODUCT & DESCRIPTION	Count	Spec	Count in spec	% meeting spec
John Smith	Apples	25	0.40	22	88
John Smith	Grapes	1	N/A	19	1900

 Thanks in advance.

Ross

Answer 1

Check for the N/A first (use for all your formulas) and return 0 or N/A

=IF(D2="N/A",0,OLD_FORMULA)

=IF(D2="N/A","N/A",OLD_FORMULA)

=IF(D2="N/A",0,COUNTIFS(Table1_[Results],"<="&D2,Table1[Supplier],$A2,Table1[Product & Description],$B2))

=IF(D2="N/A","N/A",COUNTIFS(Table1_[Results],"<="&D2,Table1[Supplier],$A2,Table1[Product & Description],$B2))