Countif Based on Date Range

  • Thread starter Thread starter Daniel
  • Start date Start date
D

Daniel

Hey Hey,

Some how I'm tangled up in a work project and I could use some help with a
formula. We are trying to "count" each supervisors "safe" and "unsafe"
actions based on a date range.

Sheet in question is called "summary". A3 & B3 = date range, C6 & C7 are
where the forumlas are places. We have this working in Excel 2007, but it
DOESN'T work in 2003 and it needs to work in 2003. I get a bunch of errors
within the forumla.

If anyone could help make this 03 compatible, I would REALLY appreciate it.
:-)

C6 Formula:
=(COUNTIFS('D. Warner'!A:A,"<"&$B$3+1,'D. Warner'!A:A,">"&$A$3-1,'D.
Warner'!D:D,"Safe"))+(COUNTIFS('P. Winkler'!A:A,"<"&$B$3+1,'P.
Winkler'!A:A,">"&$A$3-1,'P. Winkler'!D:D,"Safe"))+(COUNTIFS('R.
Freeman'!A:A,"<"&$B$3+1,'R. Freeman'!A:A,">"&$A$3-1,'R.
Freeman'!D:D,"Safe"))+(COUNTIFS('M. Pfeifer'!A:A,"<"&$B$3+1,'M.
Pfeifer'!A:A,">"&$A$3-1,'M. Pfeifer'!D:D,"Safe"))+(COUNTIFS('T.
McCanna'!A:A,"<"&$B$3+1,'T. McCanna'!A:A,">"&$A$3-1,'T.
McCanna'!D:D,"Safe"))+(COUNTIFS('N. Rasmussen'!A:A,"<"&$B$3+1,'N.
Rasmussen'!A:A,">"&$A$3-1,'N. Rasmussen'!D:D,"Safe"))+(COUNTIFS('M.
Parra'!A:A,"<"&$B$3+1,'M. Parra'!A:A,">"&$A$3-1,'M.
Parra'!D:D,"Safe"))+(COUNTIFS('M. Kieser'!A:A,"<"&$B$3+1,'M.
Kieser'!A:A,">"&$A$3-1,'M. Kieser'!D:D,"Safe"))+(COUNTIFS('B.
Scheppler'!A:A,"<"&$B$3+1,'B. Scheppler'!A:A,">"&$A$3-1,'B.
Scheppler'!D:D,"Safe"))

C7 Forumla:
=(COUNTIFS('D. Warner'!A:A,"<"&$B$3+1,'D. Warner'!A:A,">"&$A$3-1,'D.
Warner'!D:D,"Unsafe"))+(COUNTIFS('P. Winkler'!A:A,"<"&$B$3+1,'P.
Winkler'!A:A,">"&$A$3-1,'P. Winkler'!D:D,"Unsafe"))+(COUNTIFS('R.
Freeman'!A:A,"<"&$B$3+1,'R. Freeman'!A:A,">"&$A$3-1,'R.
Freeman'!D:D,"Unsafe"))+(COUNTIFS('M. Pfeifer'!A:A,"<"&$B$3+1,'M.
Pfeifer'!A:A,">"&$A$3-1,'M. Pfeifer'!D:D,"Unsafe"))+(COUNTIFS('T.
McCanna'!A:A,"<"&$B$3+1,'T. McCanna'!A:A,">"&$A$3-1,'T.
McCanna'!D:D,"Unsafe"))+(COUNTIFS('N. Rasmussen'!A:A,"<"&$B$3+1,'N.
Rasmussen'!A:A,">"&$A$3-1,'N. Rasmussen'!D:D,"Unsafe"))+(COUNTIFS('M.
Parra'!A:A,"<"&$B$3+1,'M. Parra'!A:A,">"&$A$3-1,'M.
Parra'!D:D,"Unsafe"))+(COUNTIFS('M. Kieser'!A:A,"<"&$B$3+1,'M.
Kieser'!A:A,">"&$A$3-1,'M. Kieser'!D:D,"Unsafe"))+(COUNTIFS('B.
Scheppler'!A:A,"<"&$B$3+1,'B. Scheppler'!A:A,">"&$A$3-1,'B.
Scheppler'!D:D,"Unsafe"))

Example sheet: http://rockee.endlessobsessions.com/test/Pillars.zip
 
=SUMPRODUCT(('D. Warner'!$A$2:$A$50000<$B$3+1)*('D.
Warner'!$A$2:$A$50000>$A$3-1)*('D.
Warner'!$D$2:$D$50000="Safe"))+SUMPRODUCT(('P.
Winkler'!$A$2:$A$50000<$B$3+1)*('P. Winkler'!$A$2:$A$50000>$A$3-1)*('P.
Winkler'!$D$2:$D$50000="Safe"))+SUMPRODUCT(('R.
Freeman'!$A$2:$A$50000<$B$3+1)*('R. Freeman'!$A$2:$A$50000>$A$3-1)*('R.
Freeman'!$D$2:$D$50000="Safe"))+SUMPRODUCT(('M.
Pfeifer'!$A$2:$A$50000<$B$3+1)*('M. Pfeifer'!$A$2:$A$50000>$A$3-1)*('M.
Pfeifer'!$D$2:$D$50000="Safe"))+SUMPRODUCT(('T.
McCanna'!$A$2:$A$50000<$B$3+1)*('T. McCanna'!$A$2:$A$50000>$A$3-1)*('T.
McCanna'!$D$2:$D$50000="Safe"))+SUMPRODUCT(('N.
Rasmussen'!$A$2:$A$50000<$B$3+1)*('N. Rasmussen'!$A$2:$A$50000>$A$3-1)*('N.
Rasmussen'!$D$2:$D$50000="Safe"))+SUMPRODUCT(('M.
Parra'!$A$2:$A$50000<$B$3+1)*('M. Parra'!$A$2:$A$50000>$A$3-1)*('M.
Parra'!$D$2:$D$50000="Safe"))+SUMPRODUCT(('M.
Kieser'!$A$2:$A$50000<$B$3+1)*('M. Kieser'!$A$2:$A$50000>$A$3-1)*('M.
Kieser'!$D$2:$D$50000="Safe"))+SUMPRODUCT(('B.
Scheppler'!$A$2:$A$50000<$B$3+1)*('B. Scheppler'!$A$2:$A$50000>$A$3-1)*('B.
Scheppler'!$D$2:$D$50000="Safe"))

Is the first. Figure you can get the 2nd from there. May need to break those
formulas into multiple cells and sum them up in your desired cell due to
length.
 
Works, THANK YOU :-)

Sean Timmons said:
=SUMPRODUCT(('D. Warner'!$A$2:$A$50000<$B$3+1)*('D.
Warner'!$A$2:$A$50000>$A$3-1)*('D.
Warner'!$D$2:$D$50000="Safe"))+SUMPRODUCT(('P.
Winkler'!$A$2:$A$50000<$B$3+1)*('P. Winkler'!$A$2:$A$50000>$A$3-1)*('P.
Winkler'!$D$2:$D$50000="Safe"))+SUMPRODUCT(('R.
Freeman'!$A$2:$A$50000<$B$3+1)*('R. Freeman'!$A$2:$A$50000>$A$3-1)*('R.
Freeman'!$D$2:$D$50000="Safe"))+SUMPRODUCT(('M.
Pfeifer'!$A$2:$A$50000<$B$3+1)*('M. Pfeifer'!$A$2:$A$50000>$A$3-1)*('M.
Pfeifer'!$D$2:$D$50000="Safe"))+SUMPRODUCT(('T.
McCanna'!$A$2:$A$50000<$B$3+1)*('T. McCanna'!$A$2:$A$50000>$A$3-1)*('T.
McCanna'!$D$2:$D$50000="Safe"))+SUMPRODUCT(('N.
Rasmussen'!$A$2:$A$50000<$B$3+1)*('N. Rasmussen'!$A$2:$A$50000>$A$3-1)*('N.
Rasmussen'!$D$2:$D$50000="Safe"))+SUMPRODUCT(('M.
Parra'!$A$2:$A$50000<$B$3+1)*('M. Parra'!$A$2:$A$50000>$A$3-1)*('M.
Parra'!$D$2:$D$50000="Safe"))+SUMPRODUCT(('M.
Kieser'!$A$2:$A$50000<$B$3+1)*('M. Kieser'!$A$2:$A$50000>$A$3-1)*('M.
Kieser'!$D$2:$D$50000="Safe"))+SUMPRODUCT(('B.
Scheppler'!$A$2:$A$50000<$B$3+1)*('B. Scheppler'!$A$2:$A$50000>$A$3-1)*('B.
Scheppler'!$D$2:$D$50000="Safe"))

Is the first. Figure you can get the 2nd from there. May need to break those
formulas into multiple cells and sum them up in your desired cell due to
length.
Click to expand...
 
Back
Top