Count Instances of value in two columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

KN - 23 Oct 2007 15:43 GMT

Hi,

I am trying to count the occurrences of a word in two columns. For
example if column A has the value "YES" and column B also has the
value "YES" then I want to count that as one instance of meeting the
condition that both columns must be YES in order to count the instance
in a third column. So the value of column C would be "1".

I can easily do this for one column using the COUNTIF function as
follows:

=COUNTIF(App_Inventory!A1:A10, "YES")

Thanks
KN

Reply to this Message

Peo Sjoblom - 23 Oct 2007 15:49 GMT

Please refrain from multiposting, you have an answer in you other post

Signature

Regards,

Peo Sjoblom

> Hi,
>
[quoted text clipped - 11 lines]
> Thanks
> KN

Reply to this Message

Bernard Liengme - 23 Oct 2007 15:58 GMT

To count the number of times YES appears in A1:B1
=COUNTIF(A1:B1,"YES")
If you want to display 1 when both have YES and zero otherwise
=ROUNDDOWN(COUNTIF(A1:B1,"YES")/2,0)

If you want to know how many pairs of YESs appear in A1:B10
=SUMPRODUCT(--(A1:A10="YES"),--(B1:B10="YES"))
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> Hi,
>
[quoted text clipped - 11 lines]
> Thanks
> KN

Reply to this Message

Alexander Wolff - 23 Oct 2007 18:57 GMT

> I am trying to count the occurrences of a word in two columns. For
> example if column A has the value "YES" and column B also has the
> value "YES" then I want to count that as one instance of meeting the
> condition that both columns must be YES in order to count the instance
> in a third column. So the value of column C would be "1".

C1: =--(A1="YES")*(B1="YES") or
C1: {=--AND(A1:B1="YES")} as a good one if dealing with >2 "YES" columns

Signature

Moin+Gruss Alexander - MVP für MS Excel - www.xxcl.de - mso2000sp3 --7-2

Reply to this Message

Rate this thread: