Countif Based on Date Range

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
 
S

Sean Timmons

=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.
 
D

Daniel

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Return a value based on 2 or more columns of information 2
Search range, find value in another col in corresponding row 3
Summing cells based on mulitiple criteria over mulitple columns 1
league table sort 0
COUNTIFS-Want to remove several words in one column w/multi criter 3
VBA to Find Key Words in Word doc then Extract Content to Excel.xlsm file 3
Looking to added up based on a character from cell range 1
Report row results based on the last data entry in a column 2

Top