| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| count a value in a date range based on a date in another workshee. | Excel Worksheet Functions | |||
| COUNTIF by date range problem | Excel Worksheet Functions | |||
| Countif function - using a date range | Excel Worksheet Functions | |||
| Countif with date range criteria | Excel Worksheet Functions | |||
| countif date range | Excel Worksheet Functions | |||
Linear Mode
