Sumproduct return value if data has blank cell

[SimChengKeng]

Hi All

I am trying to use the sumproduct on a range of date which to meet the criteria. My criteria is that the join date and termination date of the staff must be more than the date given and include with blank cell.

However, my date column include a blank cell, a staff who is still active. I tried many way and looking for the alternative on the internet. The value still return as #value!. Your assistance is highly appreciated

Thanks and Best Regards,
Kenneth

[azumi]

Not sure but try

=SUMPRODUCT((JoinDate>=A15)*(Termination_Date<=B15)*(Name1<>""))

[FlameRetired]

I am also not sure. Try array entering this formula.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

[Kaper]

As I'm not sure if that is the same file I've answered in the duplicated post (see rule 5 of our https://www.excelforum.com/forum-rul...rum-rules.html ): https://www.excelforum.com/excel-gen...ank-cells.html

[SimChengKeng]

Thank you so much for your expertise, Azumi,

However, i encountered another challenge. I tried to use a formula to extract data that meet the criteria in a new location of my worksheet. However,the data didnt return as expected.
On my date column include a blank cell, a staff who is still active. I want the data of those staff who is still attached to the company whereby join date and termination date to be more than the date given and include the staff who has blank cell in termination date col
Attached is my worksheet

The result should be shown as follow :

Name1 JoinDate Termination Date
Rezwan 7/23/2018 10/7/2018
Hanafi 7/16/2018 10/4/2018
Rosman 7/15/2018 10/4/2018
Mazlan 8/29/2018

[Glenn Kennedy]

Please DO NOT duplicate questions in separate threads. This can be a huge TIME WASTER for those who are trying to help you. People who do this regularly soon get to be ignored. You already have two replies to this Q on your parallel thread.