I am having the following situation:

A B
1 q 3
2 w 1
3 e
4 r 1
5 w 1
6 q 2
7 r 3
8 e
9 w 2
10 e 1
11 q
12 r 3
13 w 2
14 w
15 q 1

When used like array formula, SUM function
{=SUM((B:B)*(A:A="w"))} returns value 6

Following the logic, I tried the same aproach with COUNTA function,
but it does not work the same way

{=COUNTA((B:B)*(A:A="w"))} returns value 15 instead of value 4


I need to count number of nonblank cells in column B where "w" is respective
value in column A

In any version of Excel, this non-array formula
=SUMPRODUCT(--(A1:A150="w"),--(B1:B150<""""))
In Excel 2007 only
=SUMPRODUCT(--(A:A="w"),--(B:B<""""))
or
=COUNTIFS(A:A,"w",B:B,"<""")
that is 3 double-quotes at the end


best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Danko Jotanovic" wrote in
message ...
I am having the following situation:

A B
1 q 3
2 w 1
3 e
4 r 1
5 w 1
6 q 2
7 r 3
8 e
9 w 2
10 e 1
11 q
12 r 3
13 w 2
14 w
15 q 1

When used like array formula, SUM function
{=SUM((B:B)*(A:A="w"))} returns value 6

Following the logic, I tried the same aproach with COUNTA function,
but it does not work the same way

{=COUNTA((B:B)*(A:A="w"))} returns value 15 instead of value 4


I need to count number of nonblank cells in column B where "w" is
respective
value in column A

Instead of using your first array, take a look at SUMIF/COUNTIF
=SUMIF(A:A,"w",B:B)

Since you have two conditions (nonblank, w in A:A) need to use SUMPRODUCT
=SUMPRODUCT(--(A2:A200="w"),--(NOT(ISBLANK(B2:B200))))

Note that you can't callout entire columns (A:A) using SUMPRODUCT, unless
using XL 2007.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Danko Jotanovic" wrote:

I am having the following situation:

A B
1 q 3
2 w 1
3 e
4 r 1
5 w 1
6 q 2
7 r 3
8 e
9 w 2
10 e 1
11 q
12 r 3
13 w 2
14 w
15 q 1

When used like array formula, SUM function
{=SUM((B:B)*(A:A="w"))} returns value 6

Following the logic, I tried the same aproach with COUNTA function,
but it does not work the same way

{=COUNTA((B:B)*(A:A="w"))} returns value 15 instead of value 4


I need to count number of nonblank cells in column B where "w" is respective
value in column A

thanks to Bill Kuunders & Shane Devenshire in their reply to Studebaker I
have the result i needed:

=SUMPRODUCT((B:B<"")*(A:A="w"))

well done mates



"Danko Jotanovic" wrote:

I am having the following situation:

A B
1 q 3
2 w 1
3 e
4 r 1
5 w 1
6 q 2
7 r 3
8 e
9 w 2
10 e 1
11 q
12 r 3
13 w 2
14 w
15 q 1

When used like array formula, SUM function
{=SUM((B:B)*(A:A="w"))} returns value 6

Following the logic, I tried the same aproach with COUNTA function,
but it does not work the same way

{=COUNTA((B:B)*(A:A="w"))} returns value 15 instead of value 4


I need to count number of nonblank cells in column B where "w" is respective
value in column A