Counting based on date ranges and other values in adjacent cells

[rpalmer]

I'm trying to get an excel ss to count the number of times a certai
value appears in adjacent cells of an array based on dates. See th
snippit below:

1/2 BP 1/9 LG 1/22 DA 1/27 BR 2/3 FR 2/17 CA
1/2 BP 1/9 LG 1/22 DA 1/27 BR 2/3 FR 2/17 CA
1/2 BP 1/9 LG 1/22 DA 1/27 BR 2/3 FR 2/17 CA
1/2 BP 1/9 LG 1/22 DA 1/27 BR 2/3 FR 2/17 CA
1/2 BP 1/9 LG 1/22 DA 1/27 BR 2/3 FR 2/17 CA
1/2 BP 1/9 LG 1/22 DA 1/27 BR 2/3 FR 2/17 CA
1/2 BP 1/9 LG 1/22 DA 1/27 BR 2/3 FR 2/17 CA
12/30 BP 12/30 BP 12/30 BP 1/2 BP 1/9 LG 1/22 DA 1/27 BR 2/3 FR 2/17 CA
12/30 BP 12/30 BP 12/30 BP 1/2 BP 1/9 LG 1/22 DA 1/27 BR 2/3 FR 2/17 CA
1/2 BP 1/9 LG 1/22 DA 1/27 BR 2/3 FR 2/17 CA
1/2 BP 1/9 LG 1/22 DA 1/27 BR 2/3 FR 2/17 CA

Now my goal is to be able to count the number of times "BP" appears i
an array of cells based on if the date falls within a range (i.e

=01/01, <=03/31).

Can anyone help me?

TIA:confused

 

[Frank Kabel]

Hi
looks like a deja vu (a quite similar question was asked some minutes
ago). But try
=SUMPRODUCT((A1:Q1000>=DATE(2004,1,1))*(A1:Q1000<DATE(2004,4,1))*(B1:R1
000="CA"))
note the different ranges used in the above formula (the last range is
shifted one column to the right)

 

[rpalmer]

Thanks Frank...It works just as you said it would. Much Appreciated