Formula for counting where 2 criteria must be met

[lukerees83]

I am new to the forum and cannot work out exactly how to copy & paste in a section of a workbook, but I have managed to paste in a snippet of the columns I need and will try to be as clear as possible with my question:

The 2 columns below represent data for trains coming in and out of a station. Column O shows how much time we had from its arrival to turn the train around and get it back out of the station on time, and column P shows the number of minutes before or after its booked departure time it actually left (lateness is shown in red).

I need three formulae to count the number of trains that went late in column P based 3 different amounts of time they had to be turned around: 0-30m, 31-40m & 41-50m. I will put these formulae in cells S5, T5 & U5 so if I've got it right the formulae should count the following:

S5: if the value in column O is greater than zero and less than or equal to 30 (i.e. the turnaround time was very tight) AND the value in column P is greater than zero (i.e. the train went late). Based on the cells I have pasted in here the answer should be 2.

T5: if the value in column O is greater than or equal to 31 and less than or equal to 40 AND the value in column P is greater than zero. Answer should be 1.

U5: if the value in column O is greater than or equal to 41 and less than or equal to 50 AND the value in column P is greater than zero. Answer should be 1.

(if the number in column O is negative it means of course that the train arrived so late that we had no turnaround time at all, so by definition the train went back out late, so I don't need a formula for this).

I hope this is clear, like I said I'm new the the forum, and am grateful to anyone who can help, and explain things simply for my tiny brain
<TABLE style="WIDTH: 132pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=176><COLGROUP><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 66pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=21 width=88></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 66pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=88></TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=22> O</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65> P</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl69 height=20>Turnaround</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl74>Dept Time</TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl70 height=21> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl67> </TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 16.5pt; COLOR: #75923c; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 1pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl71 height=22>37</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND: red; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; mso-pattern: black none" class=xl73>4</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 16.5pt; COLOR: #538ed5; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 1pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl72 height=22>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND: red; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; mso-pattern: black none" class=xl68>24</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 16.5pt; COLOR: red; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 1pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl70 height=22>-37</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND: red; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid; mso-pattern: black none" class=xl67>6</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 16.5pt; COLOR: red; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 1pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl70 height=22>-33</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND: red; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid; mso-pattern: black none" class=xl67>16</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 16.5pt; COLOR: #538ed5; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 1pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl70 height=22>11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND: red; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid; mso-pattern: black none" class=xl67>4</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 16.5pt; COLOR: #538ed5; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 1pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl70 height=22>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND: #92d050; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid; mso-pattern: black none" class=xl67>-1</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 16.5pt; COLOR: #538ed5; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 1pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl70 height=22>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND: #92d050; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid; mso-pattern: black none" class=xl67>0</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 16.5pt; COLOR: #ffc000; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 1pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl70 height=22>42</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND: #92d050; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid; mso-pattern: black none" class=xl67>-1</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 16.5pt; COLOR: #ffc000; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 1pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl70 height=22>47</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND: red; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid; mso-pattern: black none" class=xl67>2</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 16.5pt; COLOR: #538ed5; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 1pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl70 height=22>13</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND: #92d050; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid; mso-pattern: black none" class=xl67>0</TD></TR></TBODY></TABLE>

 

[Patrao]

Hi,

I don't know how to get the resul you wont in a single formula, but you could use the folowing formulas to get one's when true and than just have a simple sum formula for each of the columns.

So on S5 =IF(AND(O18>0,O18<=31),1,"")

So on T5: =IF(AND(O18>31,O18<=40),1,"")

So on U5: =IF(AND(O18>11,O18<=50),1,"")

Hope this helps
Patrao

 

[njimack]

S5: =sumproduct(--($o$3:$o$12>0),--($o$3:$o$12<=30),--($p$3:$p$12>0))

t5: =sumproduct(--($o$3:$o$12>=31),--($o$3:$o$12<=40),--($p$3:$p$12>0))

u5: =sumproduct(--($o$3:$o$12>=41),--($o$3:$o$12<=50),--($p$3:$p$12>0))

 

[lukerees83]

Ok I'll try that thanks njimack

 

[Patrao]

On my aproach just try the following:


So on S5 =IF(AND(O3>0,O3<=30,P3>0),1,"")

So on T5: =IF(AND(O3>31,O3<=40,P3>0),1,"")

So on U5: =IF(AND(O3>11,O3<=50,P3>0),1,"")

 

[lukerees83]

njimack it seems that your formula works however I only pasted in 12 rows as a snippet of my total spreadsheet. The real thing is hundreds of rows down, so is there a way to amend it so it calculates the whole way down columns O & P?

Many thanks,
L

 

[njimack]

SUMPRODUCT doesn't accept entire columns as valid ranges, which is just as well because you very rarely need to refer to 65536 rows of data (or 1 million rows if you're using Excel 2007 or 2010).

Consider using dynamic ranges - http://contextures.com/xlNames01.html#Dynamic

 

[lukerees83]

Actually I may have exagerrated when I said hundreds deep, could I have the same formulas but that calculate from row 3 down to row 200 instead of putting in the whole column?