Countif(and

  • Thread starter Help with Countif(and
  • 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".
 
J

John

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

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.
 
X

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
 
T

Teethless mama

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

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

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top