Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Juana Cafe
 
Posts: n/a
Default Need formula that Counts items matching criteria using two columns

The formula in Column D should lookup the name in Column C, go to Column A
and find the corresponding name(s), then count the number of percents
opposite the corresponding name in Column B that are less than 90%. (I did a
manual count in Column D as illustration of what the final result should look
like.) Thanks !!!

Column A Column B Column C Column D (Formula
column)
Name Percent earnings Criteria Count
SMITH K 106% SMITH T 2
SMITH T 101% SMITH K
CHILDERS 97% ANDERSON 1
SMITH T 92% NEWTON
SMITH T 87% LEVINE 1
SMITH T 76% HOGAN
SMITH T 102% CHRISTOS
LEVINE 60% CHILDS
SMITH T 100% CATALDY
ANDERSON 77% BAKERS

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sloth
 
Posts: n/a
Default Need formula that Counts items matching criteria using two columns

=SUMPRODUCT(--(A2:A11=C2),--(B2:B11<0.9))

You will need to change the formating to the following custom format to show
a blank instead of zeros

General;-General;

"Juana Cafe" wrote:

The formula in Column D should lookup the name in Column C, go to Column A
and find the corresponding name(s), then count the number of percents
opposite the corresponding name in Column B that are less than 90%. (I did a
manual count in Column D as illustration of what the final result should look
like.) Thanks !!!

Column A Column B Column C Column D (Formula
column)
Name Percent earnings Criteria Count
SMITH K 106% SMITH T 2
SMITH T 101% SMITH K
CHILDERS 97% ANDERSON 1
SMITH T 92% NEWTON
SMITH T 87% LEVINE 1
SMITH T 76% HOGAN
SMITH T 102% CHRISTOS
LEVINE 60% CHILDS
SMITH T 100% CATALDY
ANDERSON 77% BAKERS

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bpeltzer
 
Posts: n/a
Default Need formula that Counts items matching criteria using two columns

=sumproduct(--(A$1:A$1000=C2),--(B$1:B$1000<0.9))
Enter that in D2 to get the count for SMITH T, then copy down as many rows
as you need. Also, change the row range to cover your entire dataset
(sumproduct can't take entire columns as input, though you could use
$A$1:$A$65536 if necessary).

"Juana Cafe" wrote:

The formula in Column D should lookup the name in Column C, go to Column A
and find the corresponding name(s), then count the number of percents
opposite the corresponding name in Column B that are less than 90%. (I did a
manual count in Column D as illustration of what the final result should look
like.) Thanks !!!

Column A Column B Column C Column D (Formula
column)
Name Percent earnings Criteria Count
SMITH K 106% SMITH T 2
SMITH T 101% SMITH K
CHILDERS 97% ANDERSON 1
SMITH T 92% NEWTON
SMITH T 87% LEVINE 1
SMITH T 76% HOGAN
SMITH T 102% CHRISTOS
LEVINE 60% CHILDS
SMITH T 100% CATALDY
ANDERSON 77% BAKERS

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Juana Cafe
 
Posts: n/a
Default Need formula that Counts items matching criteria using two col

Thanks so much !!!

"Sloth" wrote:

=SUMPRODUCT(--(A2:A11=C2),--(B2:B11<0.9))

You will need to change the formating to the following custom format to show
a blank instead of zeros

General;-General;

"Juana Cafe" wrote:

The formula in Column D should lookup the name in Column C, go to Column A
and find the corresponding name(s), then count the number of percents
opposite the corresponding name in Column B that are less than 90%. (I did a
manual count in Column D as illustration of what the final result should look
like.) Thanks !!!

Column A Column B Column C Column D (Formula
column)
Name Percent earnings Criteria Count
SMITH K 106% SMITH T 2
SMITH T 101% SMITH K
CHILDERS 97% ANDERSON 1
SMITH T 92% NEWTON
SMITH T 87% LEVINE 1
SMITH T 76% HOGAN
SMITH T 102% CHRISTOS
LEVINE 60% CHILDS
SMITH T 100% CATALDY
ANDERSON 77% BAKERS

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Juana Cafe
 
Posts: n/a
Default Need formula that Counts items matching criteria using two col

Thanks so much for all your help !!

"bpeltzer" wrote:

=sumproduct(--(A$1:A$1000=C2),--(B$1:B$1000<0.9))
Enter that in D2 to get the count for SMITH T, then copy down as many rows
as you need. Also, change the row range to cover your entire dataset
(sumproduct can't take entire columns as input, though you could use
$A$1:$A$65536 if necessary).

"Juana Cafe" wrote:

The formula in Column D should lookup the name in Column C, go to Column A
and find the corresponding name(s), then count the number of percents
opposite the corresponding name in Column B that are less than 90%. (I did a
manual count in Column D as illustration of what the final result should look
like.) Thanks !!!

Column A Column B Column C Column D (Formula
column)
Name Percent earnings Criteria Count
SMITH K 106% SMITH T 2
SMITH T 101% SMITH K
CHILDERS 97% ANDERSON 1
SMITH T 92% NEWTON
SMITH T 87% LEVINE 1
SMITH T 76% HOGAN
SMITH T 102% CHRISTOS
LEVINE 60% CHILDS
SMITH T 100% CATALDY
ANDERSON 77% BAKERS

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Create list of text matching criteria MichaelG Excel Worksheet Functions 5 March 13th 06 07:00 PM
Match then lookup Tenacity Excel Worksheet Functions 9 December 3rd 05 06:30 AM
retrieve unique items with 2 criteria Dave Breitenbach Excel Worksheet Functions 10 December 1st 05 12:16 AM
Data Validation - Scroll in the formula bar for a custom criteria Hanno Scholtz Excel Worksheet Functions 3 September 22nd 05 02:11 PM
Find largest alphanumeric value matching alpha criteria in databas Alison Excel Worksheet Functions 7 August 4th 05 06:59 PM


All times are GMT +1. The time now is 10:55 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"