Frequency with multi conditions

[GB]

Hi,
the following attachment on sheet2, column C is calculating the number of times a number falls within the range in column A & B as referenced from sheet1, Column B.

What I want to do is add the following conditions the current formula in sheet2 (highlighted in green)...
- If sheet2, A2 contains a date then only find values from sheet1, column B that are equal to or earlier than this date. If the date is blank then get all values and
- only count values from sheet1, column B if the corresponding value in column c is greater than column D.

Can you help?
regards
GB

 

[Somendra Misra]

Hi,

GB,

Are you looking for a single formula satisfying both the conditions or two formulas?

Regards,

 

[Somendra Misra]

Just check the file. Orange cells has cond. 1 formula and yellow cells has Condition 1 +2 formula. Verify the results.

Also note your green range formula is using FREQUENCY function which returns an array of result, so what you are looking at single value is actual 3 values array. Select the any cell formula in formula bar and press F9 to see the array. If you use this cell in any further calculations you may land up in an error. If you just require the count between two numbers you can use COUNTIFS or SUMPRODUCT like I have done.

Just advise if I am missing anything here.

Regards,