Count if 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]

Answered in microsoft.public.excel.worksheetfunctions. Please do not
post the same question separately to multiple newsgroups. It fragments
the thread, and leads to people wasting time constructing answers to
questions that have already been answered. Besides, the people who
answer questions generally read multiple newsgroups, so multi-posting
does not improve your odds of getting an answer.

Jerry

 

[Richard Kingslake]

Create a new column, say AA = column A concatenated with column U. Then
countif AA="xyzthis is a test"

 

[immanuel]

Try the following array formula:

[=SUM((A1:A100="xyz")*(U1:U100="this is a test"))]

Remember to use Ctrl-Shift-Enter after typing the formula in the desired
cell.

/i.