Count if two criteria match - EXCEL formula

37
votes

I have this table and I would like to create a formula which would count values based on true conditions from column A and column C.

Example: If in column A value is M (male), and in column C is YES, then it would count.

could anyone help me with this formula?

table

2
excelexcel-formula
possible duplicate of Count all fields in an column, except for n specified range – chris neilsen
There's quite a few approaches. What version of Excel? You do need to accept some answers. – Doug Glancy
Checkout Frequency Method. Single line formula instead of adding extra column and calculation. stackoverflow.com/questions/14588261/… – Mowgli

2 Answers

55
votes

Add the sheet name infront of the cell, e.g.:

=COUNTIFS(stock!A:A,"M",stock!C:C,"Yes")

Assumes the sheet name is "stock"

13
votes

If youR data was in A1:C100 then:

Excel - all versions

=SUMPRODUCT(--(A1:A100="M"),--(C1:C100="Yes"))

Excel - 2007 onwards

=COUNTIFS(A1:A100,"M",C1:C100,"Yes")