live update for excel formula

[Maxwal]

Hello,

I'm using the formula sum product to follow the performance of my department.

I use this formula to count the number of files I have to complete by counting blank cells.

But every time my team adds a new line, I have to change manually the selected cells into my formula.

How can I adapt automatically my formula when someone add a row in the array?

Thanks in advance

[#2]

@Maxwal 

Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

[#3]

@Hans Vogelaar 

Here is my data sheet. My teams add a line for every new product. And when they close the Production Order they enter a date in column B. If there is no date it means that it's still not closed. Except in the case someone add an "x" into the colum D. Then it is counted as closed.

So I have the following board to track the number of open Production Order:

My problem is that the formula need to be manually updated everytime someone add a new line in my data sheet. How to update this formula automatically?

Hope you get my point.

Thanks in advance.

[#4 · Accepted solution]

@Maxwal 

Try

=SUMPRODUCT(('EFFER visual'!$A$5:$A$1000<>"")*('EFFER visual'!$B$5:$B$1000="")*('EFFER visual'!$D$5:$D$1000=""))

[#5]

Make sense !! Thanks a lot !