countif
 

A B (TRS) C
D E F
194424C200789043 442419 1120 SE 13TH CT SUSPECT
194424C400737042 442419 1510 SE 11TH AVE SUSPECT
194424C400747034 442419 1023 SE 15TH ST SUSPECT
194424C400737027 442419 1012 SE 16TH ST SUSPECT
194424C400737044 442419 1506 SE 11TH AVE SUSPECT
194424C400730046 442419 1311 SE 11TH AVE SUSPECT
244423C101152030 442324 401 SE 8TH TER SUSPECT
144523C300180014 452314 145 SW 52ND ST POSITIVE


I only want to count the entries in column B is column F is suspect,
but I don't want to count the same entry in column B twice. So in the
end I want the number of suspect TRS.

thanks,
Matthew


--
albritton_99
------------------------------------------------------------------------
albritton_99's Profile: http://www.excelforum.com/member.php...o&userid=16139
View this thread: http://www.excelforum.com/showthread...hreadid=275738

=SUM(--(FREQUENCY(IF((F2:F9="SUSPECT")*(B2:B9<""),MATCH( B2:B9,B2:B9,0)),ROW(INDIRECT("1:"&ROWS(B2:B9)))-1)0))

which you need to confirm with control+shift+enter instead of just with
enter.

albritton_99 Wrote:
A B (TRS) C
D E F
194424C200789043 442419 1120 SE 13TH CT SUSPECT
194424C400737042 442419 1510 SE 11TH AVE SUSPECT
194424C400747034 442419 1023 SE 15TH ST SUSPECT
194424C400737027 442419 1012 SE 16TH ST SUSPECT
194424C400737044 442419 1506 SE 11TH AVE SUSPECT
194424C400730046 442419 1311 SE 11TH AVE SUSPECT
244423C101152030 442324 401 SE 8TH TER SUSPECT
144523C300180014 452314 145 SW 52ND ST POSITIVE


I only want to count the entries in column B is column F is suspect,
but I don't want to count the same entry in column B twice. So in the
end I want the number of suspect TRS.

thanks,
Matthew


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=275738