Counting cell values based on adjacent cell value over multiple columns

  • Thread starter Thread starter h2oskier
  • Start date Start date
H

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
 
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)
 
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
 
Back
Top