|
Countif Based on Date Range
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 |
Countif Based on Date Range
=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. "Daniel" wrote: 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 |
Countif Based on Date Range
Works, THANK YOU :-)
"Sean Timmons" wrote: =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. "Daniel" wrote: 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 |
Countif Based on Date Range
Not a problem. Thank you for the feedback!
"Daniel" wrote: Works, THANK YOU :-) "Sean Timmons" wrote: =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. "Daniel" wrote: 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 |
| All times are GMT +1. The time now is 11:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com