Countif(and

[Help with Countif(and]

I'm trying count how many times the value from one cell equals those cells in
a defined range and another cells equals the values in another defined range.

So I want to count the number of occurences where the a cell in column A="X"
and the cell in column B in the same row = "Y".

 

[Shane Devenshire]

Hi,

Try

=SUMPRODUCT(--(A="X"),--(B="Y"))

 

[John]

HI
Try this : =SUMPRODUCT(--(A1:A100="X"),--(B1:B100="Y"))
Adjust range as needed.
HTH
John

 

[T. Valko]

What are the names of the ranges?

Try this:

=SUMPRODUCT(--(Name1="x"),--(Name2="y"))

Replace Name1 and Name2 with your actual named ranges. Note that both named
ranges must resolve to be the exact same size.

 

[xlmate]

try this array formula ( confirm by Ctrl, Shift, Enter )

=COUNT(IF($A$2:$A$6="X",IF($B$2:$B$6="Y",1)))

HTH

--
Pls provide your feedback by clicking the Yes button below if this post is
helpful. This will help others to search the archives for result better.


Thank You

cheers, francis

 

[Teethless mama]

All versions Excel:
=SUMPRODUCT(--(A1:A100&B1:B100="xy"))

Excel 2007 only:
=COUNTIFS(A:A,"x",B:B,"y")

 

[Shane Devenshire]

Hi,

So there is no confusion about this A stands for column A, which displays as
A:A ad B for B:B or your range in columns A or B.

Also, if you use A:A in 2003 you will get a NUM error because Excel doesn't
allow full column references in this type of formula until 2007.

 

[T. Valko]

=SUMPRODUCT(--(A1:A100&B1:B100="xy"))

Caveat about using a formula like that:

x.....y
b....o
.......xy
c....y

The above formula would return 2. If your data may present this situation
and you want to use a formula like that concatenate a unique character that
will not appear in your data:

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