Counting cell values based on adjacent cell value over multiple columns

[h2oskier]

Hi everyone, new to this forum and hoping you can help.

I've got a spreadsheet where we keep track of patch upgrades b
individual and date. I need a quarterly report based on the data an
an trying to define the function I should use (if there is one) t
accomplish this. My spread sheet is:

A B C D E F
1 1/07 CB 2/03 RP 1/13 MR
2 5/05 DA 3/05 MR 2/23 DA
3 3/03 RP 4/27 DA 3/13 RP
4 2/05 MR 1/31 CB 4/15 CB
5 7/08 SC 2/28 RP 3/10 DA
6 8/10 MR 3/01 MR 2/05 MR
7 2/01 RP 4/04 CA 1/16 RP
8 2/01 DA 1/31 DA 2/08 RP
8 2/01 DA 2/05 DA 6/03 RP
10 4/03 RP 1/15 DA 7/09 MR
11 5/04 SC 2/16 MR 8/10 CB

Where column A is the date Patch 1 was installed and column B is th
initials of who did it. Column C is the date Patch 2 was installed an
column D is the intials of who did it. Column E is the date Patch
was installed and column F is the initials of who did it, etc...

I would like to know if there is a combination of functions that I ca
use to count the number of times "CA" appears in this spreadsheet whe
the corresponding date field is less than 04/01 for quarter 1. Quarte
2 would be dates between 04/01 and 07/01, etc

 

[Frank Kabel]

Hi
if your dates are real date values (just formated as MM/DD) and not
text values try the following for the first quarter
=SUMPRODUCT((A1:E1000>=DATE(2004,1,1))*(A1:E1000<DATE(2004,4,1))*(B1:F1
000="CA"))
note the different ranges used in the above formula (the last range is
shifted one column to the right)

 

[h2oskier]

Awesome, thanks for the quick reply. It worked beautifully.

Thanks again, also, rpalmer posted a similar request, we were actuall
working on the same thing and didn't know we were each posting at th
same time. Please disregard his post as I have forwarded your solutio
to him as well

 

[Frank Kabel]

Hi
that explains my deja-vu experience
also thanks for the feedback