Count rows with multiple criteria; comparing ranges and ranges to value in one statement

Hello Excel people!

My question is similar to many that I have seen but not exactly the same. I need to count the number of records/rows where a number of criteria are met. One of the criteria compares one range of cells with another range, and the next criteria compares one range of cells with a value.

I have a list of tasks each with planned & actual start dates, and planned and actual end dates. Here is an illustration (actual table is massive).


This Week 25-Apr > 29-Apr [this is the value I need to compare with]

Plan Strt | Plan End | Act Strt | Act End
Task 1 26-Apr | 19-Apr | 20-Apr | 26-Apr
Task 2 25-Apr | 22-Apr | 2-May | 25-Apr
Task 3 6-May | 4-Apr | 10-May | 10-May
Task 4 13-Apr | 11-Apr | 18-Apr | 18-Apr
Task 5 19-Apr | 13-Apr | 17-Apr | 26-Apr
Task 6 25-Apr | 14-Apr | 28-Apr | 2-May
Task 7 26-May | 16-May | 27-May | 18-May
[these are the ranges I need to compare]




I need to find out:

- Of the tasks completed this week, how many started earlier than planned, AND of these tasks how many finished later than planned.

It sounds simple, but I am confused about how to compare ranges and values in the same formula. I tried SUMPRODUCT, but could not figure out how to combine with COUNTIFS.
**I cannot add any columns to the table to make it easier***. I appreciate any help I can get.

Best, chomo (Tokyo)

Try this, data range A1:E7
=SUMPRODUCT(($B$1:$B$7>$D$1:$D$7)*($C$1:$C$7<$E$1:$E$7))

Thanks kvsrinivasamurthy.
This returns those records where tasks have started earlier than planned, AND finished later than planned. I also need to know if how many of these finished this week.

So there are three criteria (of which you have captured 1 & 2)>

1. All records where tasks have started earlier than planned
-AND-
2. Finished later than planned
-AND-
3. Finished this week.

Inclusion of this last criteria is where I am having problems. I thought perhaps combine SUMPRODUCT with COUNTIFS??

Please advise.

Cheers, chomo

Thanks Ankur. Please see attached file.

Seems everyone else is as stumped as I am...

Hi Dave. You are correct. the result should be 3. My error. I am looking at your formula (thank you very much). It seems to be working! However, I cannot see this considering the end date of "this week". If I wanted to input restrict results in between 2 dates as I do for criteria 1, how can I do this?

Criteria 1 Tasks that have ended this week (actual end) > (are between May-30 & Jun 3 in the xls)
Criteria 2 Start date is before planned start date
Criteria 3 Actual end date is past planned end date

Cheers,
Nic

FlameRetired is THE MAN!!! You totally nailed it! I had to change the operator before C7 to = (instead of<=), and I got 3 which is what I was expecting. Thank you very much!

Originally Posted by chomo

FlameRetired is THE MAN!!! You totally nailed it! I had to change the operator before C7 to = (instead of<=), and I got 3 which is what I was expecting. Thank you very much!

I got 3, but in the workbook you provided you said you expected 7 (not nice!). The 3 I got were: Task 230, Task 231, and Task 233