37

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

0m3r
  • 12,286
  • 15
  • 35
  • 71
Boco
  • 949
  • 6
  • 16
  • 22
  • 1
    possible duplicate of [Count all fields in an column, except for n specified range](http://stackoverflow.com/questions/9583161/count-all-fields-in-an-column-except-for-n-specified-range) – chris neilsen Apr 15 '12 at 00:55
  • There's quite a few approaches. What version of Excel? You do need to accept some answers. – Doug Glancy Apr 15 '12 at 01:28
  • Checkout Frequency Method. Single line formula instead of adding extra column and calculation. http://stackoverflow.com/questions/14588261/how-to-count-no-in-column-c-base-on-name-id/14591008#14591008 – Mowgli Jan 29 '13 at 20:19

2 Answers2

55

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"

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
pawan bhurtel
  • 590
  • 6
  • 3
  • 2
    For anybody looking for a Google Sheets solution, this worked for me: `=INDEX( SUMPRODUCT( ('Stock'! B2:B100 = "M") * ('Stock'! C2:C100 = "Yes") );1)` from [this](http://productforums.google.com/forum/#!topic/docs/ChcoMdlvKvI) thread. – nicholaschris Nov 12 '13 at 12:37
  • 7
    Wow - direct copy of two comments 6 months earlier below. Reputation scavenging – brettdj Feb 07 '14 at 09:27
  • hi how could this done using multiple countif?(I am using openoffice where countifs isnt available). – Michael Z May 04 '14 at 02:02
14

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

brettdj
  • 54,857
  • 16
  • 114
  • 177