sumproduct

K

kkondrat1

Hello,

I have a sheet that looks like this:

ID.....Code.....Code2.....Date
0.......NBH........I06........3/31/04
1.......BH1........I06........4/01/04
2.......GH1........I05........4/02/04
3.......B67.........I06........4/03/04
4.......B68.........I09........4/04/04

I want to count how many rows that have an I06 "code2" betwee
4/01/04-4/04/04, that have a "B" Code in the "first space" but not B
codes.

I am currently using an advanced filter to run this but it takes to
much time. here is how I have the advanced filter set up:

Code........Code......Date............ Date Code2
<>BH* B* >=4/01/04 <=4/31/04 I06

I attempted SUMPRODUCT, but it didn't work correctlu, any ideas?
Thank
 
F

Frank Kabel

Hi
try
=SUMPRODUCT(--(D1:D100>=DATE(2004,4,1)),--(D1:D100<=DATE(2004,4,31)),--
(C1:C100=IO6),--(ISNUMBER(FIND("B",B1:B100))),--(NOT(ISNUMBER(FIND("BH"
,B1:B100)))))
 
D

Don Guillett

try
=sumproduct((daterng>=a1)*daterng<=b1)*(left(brng,1)="b")*(left(brng,2)<>"BH
")*(rngC="io6"))
 
Top