Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create list of text matching criteria | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
retrieve unique items with 2 criteria | Excel Worksheet Functions | |||
Data Validation - Scroll in the formula bar for a custom criteria | Excel Worksheet Functions | |||
Find largest alphanumeric value matching alpha criteria in databas | Excel Worksheet Functions |