| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting # of cells with that meet criteria in two columns
Say I have two columns of data.
DISTRICT STATUS AND DATE 10 Complete 3-2-2007 10 20 Complete 9-10-2006 20 Complete 1-1-2001 I need a function or formula that counts the number of employees who have completed training by district. For district 10 the answer would be one. For district 20 the answer would be two. My greatest challenge is how to count the column that "includes" the word Complete. Many thanks for help! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting # of cells with that meet criteria in two columns
So, "complete" and the date are in the same cell?
Try this: =SUMPRODUCT(--(A2:A5=10),--(ISNUMBER(SEARCH("complete",B2:B5)))) Or: D2 = 10 E2 = complete =SUMPRODUCT(--(A2:A5=D2),--(ISNUMBER(SEARCH(E2,B2:B5)))) -- Biff Microsoft Excel MVP "Scott at Medt." <Scott at wrote in message ... Say I have two columns of data. DISTRICT STATUS AND DATE 10 Complete 3-2-2007 10 20 Complete 9-10-2006 20 Complete 1-1-2001 I need a function or formula that counts the number of employees who have completed training by district. For district 10 the answer would be one. For district 20 the answer would be two. My greatest challenge is how to count the column that "includes" the word Complete. Many thanks for help! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting # of cells with that meet criteria in two columns
The formula is:
=SUMPRODUCT(--(A2:A5=20),--(LEFT(B2:B5,8)="complete")) substitute the ranges for where you actually have data. -- JNW "Scott at Medt." wrote: Say I have two columns of data. DISTRICT STATUS AND DATE 10 Complete 3-2-2007 10 20 Complete 9-10-2006 20 Complete 1-1-2001 I need a function or formula that counts the number of employees who have completed training by district. For district 10 the answer would be one. For district 20 the answer would be two. My greatest challenge is how to count the column that "includes" the word Complete. Many thanks for help! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting # of cells with that meet criteria in two columns
This will only work if complete is always first (and always spelled right!
that's my downfall:) -- JNW "T. Valko" wrote: So, "complete" and the date are in the same cell? Try this: =SUMPRODUCT(--(A2:A5=10),--(ISNUMBER(SEARCH("complete",B2:B5)))) Or: D2 = 10 E2 = complete =SUMPRODUCT(--(A2:A5=D2),--(ISNUMBER(SEARCH(E2,B2:B5)))) -- Biff Microsoft Excel MVP "Scott at Medt." <Scott at wrote in message ... Say I have two columns of data. DISTRICT STATUS AND DATE 10 Complete 3-2-2007 10 20 Complete 9-10-2006 20 Complete 1-1-2001 I need a function or formula that counts the number of employees who have completed training by district. For district 10 the answer would be one. For district 20 the answer would be two. My greatest challenge is how to count the column that "includes" the word Complete. Many thanks for help! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting # of cells with that meet criteria in two columns
Hey--- This was meant for my post. Sorry T.
-- JNW "JNW" wrote: This will only work if complete is always first (and always spelled right! that's my downfall:) -- JNW "T. Valko" wrote: So, "complete" and the date are in the same cell? Try this: =SUMPRODUCT(--(A2:A5=10),--(ISNUMBER(SEARCH("complete",B2:B5)))) Or: D2 = 10 E2 = complete =SUMPRODUCT(--(A2:A5=D2),--(ISNUMBER(SEARCH(E2,B2:B5)))) -- Biff Microsoft Excel MVP "Scott at Medt." <Scott at wrote in message ... Say I have two columns of data. DISTRICT STATUS AND DATE 10 Complete 3-2-2007 10 20 Complete 9-10-2006 20 Complete 1-1-2001 I need a function or formula that counts the number of employees who have completed training by district. For district 10 the answer would be one. For district 20 the answer would be two. My greatest challenge is how to count the column that "includes" the word Complete. Many thanks for help! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting # of cells with that meet criteria in two columns
I belive this is working - you rock!
"T. Valko" wrote: So, "complete" and the date are in the same cell? Try this: =SUMPRODUCT(--(A2:A5=10),--(ISNUMBER(SEARCH("complete",B2:B5)))) Or: D2 = 10 E2 = complete =SUMPRODUCT(--(A2:A5=D2),--(ISNUMBER(SEARCH(E2,B2:B5)))) -- Biff Microsoft Excel MVP "Scott at Medt." <Scott at wrote in message ... Say I have two columns of data. DISTRICT STATUS AND DATE 10 Complete 3-2-2007 10 20 Complete 9-10-2006 20 Complete 1-1-2001 I need a function or formula that counts the number of employees who have completed training by district. For district 10 the answer would be one. For district 20 the answer would be two. My greatest challenge is how to count the column that "includes" the word Complete. Many thanks for help! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting # of cells with that meet criteria in two columns
Would you mind explaining to me what the three functions are doing in this
formula as well as the role of the "--" ? I have more work to do but if I understand how this works I may be able to complete this on my own. Again my thanks! Scott "T. Valko" wrote: So, "complete" and the date are in the same cell? Try this: =SUMPRODUCT(--(A2:A5=10),--(ISNUMBER(SEARCH("complete",B2:B5)))) Or: D2 = 10 E2 = complete =SUMPRODUCT(--(A2:A5=D2),--(ISNUMBER(SEARCH(E2,B2:B5)))) -- Biff Microsoft Excel MVP "Scott at Medt." <Scott at wrote in message ... Say I have two columns of data. DISTRICT STATUS AND DATE 10 Complete 3-2-2007 10 20 Complete 9-10-2006 20 Complete 1-1-2001 I need a function or formula that counts the number of employees who have completed training by district. For district 10 the answer would be one. For district 20 the answer would be two. My greatest challenge is how to count the column that "includes" the word Complete. Many thanks for help! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting # of cells with that meet criteria in two columns
Using your posted sample:
10...Complete 3-2-2007 10................................. 20...Complete 9-10-2006 20...Complete 1-1-2001 =SUMPRODUCT(--(A2:A5=10),--(ISNUMBER(SEARCH("complete",B2:B5)))) Returns 1 This is how it does that... Each of these expressions will return an array of either TRUE or FALSE: (A2:A5=10) (ISNUMBER(SEARCH("complete",B2:B5))) A2 = 10 = TRUE A3 = 10 = TRUE A4 = 10 = FALSE A5 = 10 = FALSE The SEARCH function "searches" the string for the substring "complete". This search is case insensitive. If the string contains the substring the result of SEARCH is the character number of the starting position of the substring. For example: Complete 3-2-2007 XX complete 1/1/2007 XX 1/1/2007 =SEARCH("Complete","Complete 3-2-2007") = 1 because the substring is found and starts at character position 1. =SEARCH("Complete","XX complete 1/1/2007") = 4 because the substring is found and starts at character position 4. =SEARCH("Complete","XX 1/1/2007") = #VALUE! because the substring is not found. We test the result of SEARCH to see if it is a number meaning the substring was found by wrapping SEARCH inside of ISNUMBER: (ISNUMBER(SEARCH("complete",B2))) = TRUE (ISNUMBER(SEARCH("complete",B3))) = FALSE (ISNUMBER(SEARCH("complete",B4))) = TRUE (ISNUMBER(SEARCH("complete",B5))) = TRUE SUMPRODUCT needs to work with numbers so we use the "--" to coerce the TRUE and FALSE to 1 and 0 respectively. --(A2 = 10) = 1 --(A3 = 10) = 1 --(A4 = 10) = 0 --(A5 = 10) = 0 --(ISNUMBER(SEARCH("complete",B2))) = 1 --(ISNUMBER(SEARCH("complete",B3))) = 0 --(ISNUMBER(SEARCH("complete",B4))) = 1 --(ISNUMBER(SEARCH("complete",B5))) = 1 Now we have 2 arrays of 1s and 0s. These 2 arrays are then multiplied together: =SUMPRODUCT({1;1;0;0},{1;0;1;1}) 1 * 1 = 1 1 * 0 = 0 0 * 1 = 0 0 * 1 = 0 SUMPRODUCT sums the result of multiplying these arrays: =SUMPRODUCT({1;0;0;0}) = 1 For more detailed info on SUMPRODUCT: http://xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "Scott at Medt." wrote in message ... Would you mind explaining to me what the three functions are doing in this formula as well as the role of the "--" ? I have more work to do but if I understand how this works I may be able to complete this on my own. Again my thanks! Scott "T. Valko" wrote: So, "complete" and the date are in the same cell? Try this: =SUMPRODUCT(--(A2:A5=10),--(ISNUMBER(SEARCH("complete",B2:B5)))) Or: D2 = 10 E2 = complete =SUMPRODUCT(--(A2:A5=D2),--(ISNUMBER(SEARCH(E2,B2:B5)))) -- Biff Microsoft Excel MVP "Scott at Medt." <Scott at wrote in message ... Say I have two columns of data. DISTRICT STATUS AND DATE 10 Complete 3-2-2007 10 20 Complete 9-10-2006 20 Complete 1-1-2001 I need a function or formula that counts the number of employees who have completed training by district. For district 10 the answer would be one. For district 20 the answer would be two. My greatest challenge is how to count the column that "includes" the word Complete. Many thanks for help! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I count 2 columns of data that meet a certain criteria? | Excel Discussion (Misc queries) | |||
| Counting Consecutive Cells that meet Criteria | Excel Discussion (Misc queries) | |||
| Counting lines that meet TWO criteria | Excel Worksheet Functions | |||
| Counting cells that meet 2 differnet criteria | Excel Worksheet Functions | |||
| Counting Values that meet another cells criteria | Excel Worksheet Functions | |||
Linear Mode
