Counting number of occurrences in a column

[Monsignor]

Hello!

How would you look between 2 columns and get the total number of occurrences where the date in column F is larger than the date in column B?

Specifically?

Range:
B8:F50

Column B - Planned Start Date
Column F - Actual Start Date

I want to know the total number of tasks that started late.

I tried COUNTIF but that doesn't seem right.

BTW, I'm using Excel 2010.

 

[Monsignor]

Looks like this is the solution:

{=SUM(--(B8:B50<F8:F50))}


Any warnings or observations that you might share? This is my first Array formula, and first time using the double-negative.

 

[Aladin Akyurek]

Hello!

How would you look between 2 columns and get the total number of occurrences where the date in column F is larger than the date in column B?

Specifically?

Range:
B8:F50

Column B - Planned Start Date
Column F - Actual Start Date

I want to know the total number of tasks that started late.

I tried COUNTIF but that doesn't seem right.

BTW, I'm using Excel 2010.

Just enter:

=SUMRODUCT(($F$2:$F$100 > $B$2:$B$100)+0)

 

[Monsignor]

Just enter:

=SUMRODUCT(($F$2:$F$100 > $B$2:$B$100)+0)

THANKS! Much cleaner than what I came up with.

Question: what is the role of the "+0"?

 

[Monsignor]

looks like this is the solution:

{=sum(--(b8:b50<f8:f50))}


any warnings or observations that you might share? This is my first array formula, and first time using the double-negative.

{=sum(--(b8:b50 < f8:f50))}

 

[Aladin Akyurek]

THANKS! Much cleaner than what I came up with.

You are welcome.

Question: what is the role of the "+0"?

That helps to map TRUE/FALSE values onto their 1/0 numeric equivalents.

See for more e.g.:

http://www.mrexcel.com/forum/showthread.php?t=202204

http://www.mrexcel.com/forum/showthread.php?t=128907

http://www.mrexcel.com/forum/showthread.php?t=70547

 

[Monsignor]

You are welcome.



That helps to map TRUE/FALSE values onto their 1/0 numeric equivalents.

See for more e.g.:

http://www.mrexcel.com/forum/showthread.php?t=202204

http://www.mrexcel.com/forum/showthread.php?t=128907

http://www.mrexcel.com/forum/showthread.php?t=70547

NEAT! This is fantastic! I got it. Thanks.