Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Two Characteristics from Two different Columns | Excel Discussion (Misc queries) | |||
Counting with multiple criteria in separate Columns | Excel Discussion (Misc queries) | |||
Counting based on values in two separate columns | Excel Worksheet Functions | |||
Counting values for variables in separate columns | Excel Discussion (Misc queries) | |||
Counting 2 Separate Columns With Unique Data | Excel Programming |