Counting with 2 criteria

[groundcontrol]

I need to count the number of cells that satisfy two criteria. I.e. that are both x (listed in column "A") and y (listed in column "B"). So I've tried various combinations of COUNTIF and AND, to no avail.

For example: COUNTIF(AND((A1:A100,"x"),(B1:B100,"y")))

Any help/pointers greatly appreciated!

 

[fairwinds]

Hi,

Try:

=SUMPRODUCT(--(A1:A100="x"),--(B1:B100="y"))

 

[VBA_Newbie]

The COUNTIF function won't work with multiple conditions. Some people like to use a SUMPRODUCT function. I like the SUM function. Try this:

=SUM(IF(A1:A100 = "x",IF(B1:B100="y", 1,0))

As this is an array formula, you need to press CTRL+ALT+ENTER for it to work.

How it works:

It looks at A1 and B1...if A1=x AND B1 = y...it adds 1. If not, it adds 0. It does this over and over again until A100 and B100. It counts the number of rows that countain both x in column A and y in column B. Let me know if this helps.