Formula for Counting Two Characteristics in Separate Columns

[PGiessler]

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

 

[Mike H]

Hi,

=SUMPRODUCT((B1:B20="Bob")*(D120="Active"))

With the obvious change for "Inactive"

In Practice I would use cell references for the lookup values

=SUMPRODUCT((B1:B20=F1)*(D120=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.

 

[helene and gabor]

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