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!

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!

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!

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!

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!

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!

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!

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!