Countif(and

  • Thread starter Thread starter Help with Countif(and
  • Start date Start date
H

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".
 
HI
Try this : =SUMPRODUCT(--(A1:A100="X"),--(B1:B100="Y"))
Adjust range as needed.
HTH
John
 
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.
 
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
 
All versions Excel:
=SUMPRODUCT(--(A1:A100&B1:B100="xy"))

Excel 2007 only:
=COUNTIFS(A:A,"x",B:B,"y")
 
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.
 
=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"))
 
Back
Top