Count Non-Blanks in one column based on criteria in another
 

Considering the following hypothetical table with column headers and example
values:

[A] [b] [C]
FREQ CALL1 CALL2
[1] 18 10
[2] 12 8 21
[3] 6 16
[4] 18 8 25
[5] 12 5
[6] 6

I want to know for all rows containing '18' for FREQ, how many of these have
a 'non-blank' value for CALL2.

In the above example,there is only one row that has a value of '18' for FREQ
AND a 'non-blank' for CALL2. Therefore the count is '1'

I need a straight-forward formula - not pivot table, to provide this type of
conditional count.

Any advice would be sincerely appreciated !!!!

Count Non-Blanks in one column based on criteria in another
 

=SUMPRODUCT(--(A1:A6=18), --(C1:C6<""))

adjust ranges as needed. sumproduct cannot accomodate entire columns (ie
A:A).

"ScottPcola" wrote:

Considering the following hypothetical table with column headers and example
values:

[A] [b] [C]
FREQ CALL1 CALL2
[1] 18 10
[2] 12 8 21
[3] 6 16
[4] 18 8 25
[5] 12 5
[6] 6

I want to know for all rows containing '18' for FREQ, how many of these have
a 'non-blank' value for CALL2.

In the above example,there is only one row that has a value of '18' for FREQ
AND a 'non-blank' for CALL2. Therefore the count is '1'

I need a straight-forward formula - not pivot table, to provide this type of
conditional count.

Any advice would be sincerely appreciated !!!!

Count Non-Blanks in one column based on criteria in another
 

Thanks JMB...that did the trick !!!

"JMB" wrote:

=SUMPRODUCT(--(A1:A6=18), --(C1:C6<""))

adjust ranges as needed. sumproduct cannot accomodate entire columns (ie
A:A).

"ScottPcola" wrote:

Considering the following hypothetical table with column headers and example
values:

[A] [b] [C]
FREQ CALL1 CALL2
[1] 18 10
[2] 12 8 21
[3] 6 16
[4] 18 8 25
[5] 12 5
[6] 6

I want to know for all rows containing '18' for FREQ, how many of these have
a 'non-blank' value for CALL2.

In the above example,there is only one row that has a value of '18' for FREQ
AND a 'non-blank' for CALL2. Therefore the count is '1'

I need a straight-forward formula - not pivot table, to provide this type of
conditional count.

Any advice would be sincerely appreciated !!!!

Count Non-Blanks in one column based on criteria in another
 

you're welcome - thanks for the feedback.

"ScottPcola" wrote:

Thanks JMB...that did the trick !!!

"JMB" wrote:

=SUMPRODUCT(--(A1:A6=18), --(C1:C6<""))

adjust ranges as needed. sumproduct cannot accomodate entire columns (ie
A:A).

"ScottPcola" wrote:

Considering the following hypothetical table with column headers and example
values:

[A] [b] [C]
FREQ CALL1 CALL2
[1] 18 10
[2] 12 8 21
[3] 6 16
[4] 18 8 25
[5] 12 5
[6] 6

I want to know for all rows containing '18' for FREQ, how many of these have
a 'non-blank' value for CALL2.

In the above example,there is only one row that has a value of '18' for FREQ
AND a 'non-blank' for CALL2. Therefore the count is '1'

I need a straight-forward formula - not pivot table, to provide this type of
conditional count.

Any advice would be sincerely appreciated !!!!