COUNTIF Accross Multiple Columns with AND

[relaxandflow]

I can't find the answer to this problem in any resource I have
looked...

I have two columns, E and K. Column E contains various place names, but
only if they are within a certain city; when the place is outside that
city, the cell in column E is blank. Column K contains the quarter that
a particular event occured. I want to count all the events that occured
within only that city during second quarter 2006. So, in the following
example, it would give me a count of 2 by counting only rows 3 and 5.

1 E K
2 Shinjuku 05-Q1
3 Chuo 06-Q2
4 06-Q2
5 Minato 06-Q2


Here is my attempt, but Excel doesn't like it...

=COUNTIF(AND(E2:E634="?*"),(K2:K634="06-Q2"))

Your help is much appreciated!

 

[RagDyer]

Try this:

=SUMPRODUCT((E2:E634<>"")*(K2:K634="06-Q2"))

 

[RelaxAndFlow]

I have three columns. Column A contains various place names, but only
if they are within a certain city; when the place is outside that city,
the cell in column A is blank. Column B contains the quarter that a
particular event occured. Column C contains a score for that quarter. I
want to find the average score for 2nd quarter 2006 only in that city.

A B C
1 Shinjuku 06-Q2 22
2 05-Q1 54
3 Minato 03-Q1 3
4 Chuo 06-Q2 44

So, it would give me (C1+C4)/2.

I imagined it to be like this, but it doesn't work of course:

AVERAGEIF(AND(A1:A4="?*"),(B1:B4+"06-Q2"), C:C)

Your help is greatly appreciated!

 

[RagDyer]

You could try this *array* formula:

=AVERAGE(IF((A1:A6<>"")*(B1:B6="06-Q2"),C1:C6))

 

[RelaxAndFlow]

Worked like a charm. Thank you very much!

 

[RelaxAndFlow]

Worked perfectly! Thank you again!

 

[RagDyeR]

You're welcome, and thanks for the feed-back.