COUNTIF Accross Multiple Columns with AND
 

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!

COUNTIF Accross Multiple Columns with AND
 

Try this:

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

wrote in message
oups.com...
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!

Conditional Average Accross Multiple Columns with AND
 

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!

Conditional Average Accross Multiple Columns with AND
 

You could try this *array* formula:

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

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"RelaxAndFlow" wrote in message
ups.com...
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!

COUNTIF Accross Multiple Columns with AND
 

Worked like a charm. Thank you very much!

Conditional Average Accross Multiple Columns with AND
 

Worked perfectly! Thank you again!

Conditional Average Accross Multiple Columns with AND
 

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

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"RelaxAndFlow" wrote in message
oups.com...
Worked perfectly! Thank you again!