Formula for Counting Two Characteristics in Separate Columns
 

I have a list of Sales People's Names in "Column B", in "Column C" I have the
client name and in "Column D" I have an indication of whether the account is
"Active" or "Inactive" . In a summary field I want to calculate how many
"Active" accounts "Bob" has, then in another summary field show the number of
"Inactive" accounts "Bob" has.

I have done this before, but seem to be struggling with the formula this
morning. Any help would really be appreciated.

Thanks, Paul

Formula for Counting Two Characteristics in Separate Columns
 

Hi,

=SUMPRODUCT((B1:B20="Bob")*(D1:D20="Active"))

With the obvious change for "Inactive"

In Practice I would use cell references for the lookup values

=SUMPRODUCT((B1:B20=F1)*(D1:D20=G1))

Where
F1= Bob
G1= Active
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"PGiessler" wrote:

I have a list of Sales People's Names in "Column B", in "Column C" I have the
client name and in "Column D" I have an indication of whether the account is
"Active" or "Inactive" . In a summary field I want to calculate how many
"Active" accounts "Bob" has, then in another summary field show the number of
"Inactive" accounts "Bob" has.

I have done this before, but seem to be struggling with the formula this
morning. Any help would really be appreciated.

Thanks, Paul

Formula for Counting Two Characteristics in Separate Columns
 

Hello,

Assume a table of names(B2-B10), clients (C2-C10) and status(D2-D10) , i
for inactive and a for active designation.
Name: Jack in B2.
The array formula to count Jack's inactive entries is:

=SUM(IF(($B$2:$B$10=$B$2)*($D$2:$D$10="i"),1,""))

Control/Shift/ Enter

The formula for Jack's active entries is the same but "i" is to be replaced
by "a".

Best Regards,

Gabor Sebo


"PGiessler" wrote in message
...
I have a list of Sales People's Names in "Column B", in "Column C" I have
the
client name and in "Column D" I have an indication of whether the account
is
"Active" or "Inactive" . In a summary field I want to calculate how many
"Active" accounts "Bob" has, then in another summary field show the number
of
"Inactive" accounts "Bob" has.

I have done this before, but seem to be struggling with the formula this
morning. Any help would really be appreciated.

Thanks, Paul