Counif calculation based on 2 criteria

**Stuand** · 08-25-2011, 04:44 AM

Hi all,

I have am setting up a spreadsheet which has 2 basic columns,
Column A will be either "yes" or "no",
Column B will then have 1 of 17 possible "defect" codes.

I would like to "count" the number of specific instances in column "B", but only if Column A is equal to "yes".

Iam aware of the basic "Countif(Column,"entry), but can and "and" statement be added to this?

Many thanks for any help,

Stu

**arthurbr** · 08-25-2011, 04:51 AM

Hi and welcome to the board

Have you tried a Pivot Table?

**Stuand** · 08-25-2011, 04:56 AM

Hi,

..yes I am familiar with Pivot tables, but I am writting the spreadsheet for a collegaue who has "limited / no knowledge of oivot tables. I thought that a formulae is a bit more visible...

Stu

**DeepakS** · 08-25-2011, 05:01 AM

=COUNTIFS(Sheet1!$A$1:$A$50,"Yes",Sheet1!$B$1:$B$50,"Defect Code")

**arthurbr** · 08-25-2011, 05:24 AM

Seems to me that a PT is much more intuitive than a formula, but then again...

**Stuand** · 08-25-2011, 07:44 AM

Hi, and thanks for the formula...I have tried it (as follows..."=COUNTIFS('Data Input'!E1:E6000,"yes",'Data Input'!F1:F6000,A6)"...but I just get a "#Name?".......

any other help gratefully appreciated..or am I doing something basic?

Thanks again,

Stu

**Stuand** · 08-25-2011, 07:45 AM

NB ...sorry...I have left out the "S" at the end of "countif"...but I still get a formulae error message?

Stu

**arthurbr** · 08-25-2011, 07:50 AM

Try =sumproduct(--(Sheet1!$A$1:$A$50="Yes"),--(Sheet1!$B$1:$B$50="Defect Code"))

(COUNTIFS is a XL 2007 function)

**Stuand** · 08-25-2011, 07:57 AM

..many thanks again for input, but I have tried this and still just get an error message?

Stu

**arthurbr** · 08-25-2011, 08:25 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!