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

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

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Help with Countif(and" wrote:

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
"Help with Countif(and" <Help with
wrote in message ...
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".

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.

--
Biff
Microsoft Excel MVP


"Help with Countif(and" <Help with
wrote in message ...
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".

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









"Help with Countif(and" wrote:

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

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

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


"Help with Countif(and" wrote:

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,

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Shane Devenshire" wrote:

Hi,

Try

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

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Help with Countif(and" wrote:

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

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

--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
All versions Excel:
=SUMPRODUCT(--(A1:A100&B1:B100="xy"))

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


"Help with Countif(and" wrote:

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