Counting # of cells with that meet criteria in two columns

[Scott at Medt.]

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!

 

[T. Valko]

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))))

 

[JNW]

The formula is:
=SUMPRODUCT(--(A2:A5=20),--(LEFT(B2:B5,8)="complete"))
substitute the ranges for where you actually have data.

 

[JNW]

This will only work if complete is always first (and always spelled right!
that's my downfall

 

[JNW]

Hey--- This was meant for my post. Sorry T.

 

[Scott at Medt.]

I belive this is working - you rock!

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))))

 

[Scott at Medt.]

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]

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