Multi Criteria record count. Ranges and Values. This one stumps everyone including me

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. I have tried Excel Forum, but they cannot figure it out.

I have uploaded a spreadsheet with a more records which will help to understand the problem.

Thank you so much!
chomo (Tokyo)

Correction. The result of the attached spreadsheet should be 3 (not 7). It is critical that I can select the range of dates for "this week", and the formula needs to reference these dates. Thank you!

chomocholas said:

Correction. The result of the attached spreadsheet should be 3 (not 7).

Click to expand...

I can not understand you best.
Why is result 3 in your example file?
Do you think on the Tasks 252,253,254?
Can you explain the logic in this case.
What formula are you used?
Thank you.

Try =SUMPRODUCT(--(C7:C306>D7:d306)*(E7:E306<F7:F306))

(replacing the asterisk with a comma might speed things up if needed)

Thanks Peco! This worked. I added some more code to limit the results to a particular week> =SUMPRODUCT(--(WEEKNUM(--F7:F301)=WEEKNUM($D$1)),--(D7301<=C7:C301),--(F7:F301>E7:E301)


Cheers.

You're welcome
I don't think the unary operator within the WEEKNUM function is of any use

chomocholas said:

This worked. I added some more code to limit the results to a particular week

Click to expand...

OK, you've solved the problem, and that's fine.
However, I am somewhat interested. Maybe I did not something correctly understood.

In your example file, you wrote that the expected result number 3

If I understand you well you have two conditions, in the cells D1 and E1 (this is week 22).
The formula that has given @pecoflyer returns the result 3. That is ok.
This your new formula returns the result of the number 5.

What if other tasks meet the requirement, but out of the ranking date in the D1 and E1 (week)? Then the formula SUMPRODUCT returns an incorrect result.

eg. Task 236, if there are the following dates, the formula will return the incorrect result.(this is week 21). Is possible this situation?

24-May-16 | 23-May-16 | 26-May-16 | 27-May-16

Click to expand...

Do you not want to return the number tasks that meet the requirements for a particular week (between two dates, criteria in D1 and E1)?

I have solved the problem in a different way by the formula in helper column.

Please see attached example file.
Can you explain this situation?

Hi Navic (et.al) Thank you so much for your interest in my problem. I am very much a novice so please bear with me. I found that there is typo in my formula. (D7301<=C7:C301) should be (D7301<C7:C301). I incorrectly wrote <= when it should be <. I think this solves the confusion.

I see like your "helper" - very useful. I have since expanded my formula to include a dynamic range (not a named range). My issue now is that I have a workbook, and I cannot alter it (so cannot add named ranges), but I am allowed to run an analysis from a single sheet within the workbook. I am having trouble now due to my dynamic ranges. If you have time, perhaps you could take a look at it now?

Only one criteria now but I am making a leap FROM>

'=SUMPRODUCT(--(WEEKNUM(--F8:F301)=WEEKNUM($D$1)))

TO>

'=SUMPRODUCT(--(WEEKNUM(--INDIRECT($A$1&"!$F$7:$F$"&COUNTA(INDIRECT($A$1&"!B7:B10000")))=WEEKNUM($D$1))))

I am using SUMPRODUCT because I will add more criteria later.
This part, (INDIRECT($A$1&"!$F$7:$F$"&COUNTA(INDIRECT($A$1&"!B7:B10000")), is supposed to set my range, and it seems to return a correct looking range but the whole formula does not work when together.

SSH attached.

Thanks for your help!
chomo

I was interested in something different.
Sorry, I can not understand you well. English is not my native language, so I can not with certainty you understand.

Please attach your file with the form example and expected results. If necessary, insert some comments on an important cell in which the result, then I can see which the data necessary to calculate the formula.

If you want to use the 'master' sheet and it will have results, then you can create a formula that you automatically creates a list of all sheets in the Workbook.
Hard for me to think when I do not see your actual example file.

My file is attached to the format (*.xlsm), because I used the formula for a list of all sheets in the workbook (you do not have to use this if you do not want).
Pay attention to the volatile functions