countif based on 2 criteria

[nobody]

I am attempting to summarize some data based on the values in 2 different
cells. Example Count the number of rows where column A = xyz and column U =
"this is a test"

I know the countif statement can't do multiple criteria, but is it possible
to use nested countif statements, or use some combination of AND or IF
statements?

Thank you

 

[Jerry W. Lewis]

=SUMPRODUCT((criteria1)*(criteria2)*...)

Jerry

 

[nobody]

Maybe I am doing something wrong, or this function won't work with text
When I enter
=SUMPRODUCT((A:A=xyz)*(U:U="This is a test")
I get the #NUM error
In other words, what I am trying to do is the equilivent of a statement
countif(a:a,xyz) and countif(u:u, "this is a test")

Data Example:
A B C ....... U
--------------------------------------------------------
1 abc 12 7/14/03 No good
2 xyz 7 7/14/03 this is a test
3 xyz 23 7/14/03 No good
4 abc 5 7/14/03 this is a test

The desired result based on the above data and the requested function would
be 1

Thanks

 

[nobody]

I found it

Enter this formula as an array (ctrl - alt - enter)
SUM((a:a=xyz)*(u:u="This is a test))

Thank you

 

[Jerry W. Lewis]

Neither formula that you posted should work, since array formulas cannot
address an entire column. Also, unless xyz is a defined name, it must
be in quotes.

Beyond that,
=SUMPRODUCT((A1:A100="xyz")*(U1:U100="This is a test"))
and
=SUM((A1:A100="xyz")*(U1:U100="This is a test"))
are completely equivalent, except that the SUMPRODUCT formula does not
have to be array entered.

Jerry