Counting based on date ranges and other values in adjacent cells

  • Thread starter Thread starter rpalmer
  • Start date Start date
R

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).
Click to expand...

Can anyone help me?

TIA:confused
 
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)
 
Back
Top