counting blank cells

**nralph13** · 11-25-2006, 07:57 PM

I am trying to write a formula for the following can anyone help please?
I have column D with blank cells and some with purchase order numbers in them. I want to count the blank cells in column D but only if the blank is 3days older than the date recorded in column A. Once I have done this I want to count ther same on every work sheet and I have 24 off them and get one answer.
This will tell us all the outstanding purchase order older than 3days
Thanks

**Special-K** · 11-25-2006, 10:14 PM

"I want to count the blank cells in column D but only if the blank is 3days older than..."

This doesnt make sense. It's a blank cell, it can't be "older" than anything. It's blank.

Please explain the problem more clearly.

**protonLeah** · 11-26-2006, 12:40 AM

From your post, it seems that Col-A is "Order Date" and Col-D is Completion/Fill Date. Hopefully, you have a status column? For example, using 100 rows of col-G for the status, then:

=IF(AND(G1:G100="",NETWORKDAYS(A1,TODAY())>3),"Overdue", "")

and copy down the used rows of the column.
Then in G101 you can sum the rows that contain "overdue" with:

=COUNTIF(G1:G100,"overdue")

Finally, in some cell on sheet 25 you can add the G101's of the previous 24 sheets with the array formula:

=SUM(Sheet1:Sheet24!G1)

and pressing Ctrl-Shift-Enter instead of just the enter key to accept the formula into the cell.

**Zrx** · 11-26-2006, 11:41 AM

What is the importance of the control,shift,enter part as I'm experiencing formula loss on any given cell at times. The answer also resides the cell as well. example: =sum(a1:a34) in b2 formula bar and answer in cell b2.

**VBA Noob** · 11-26-2006, 11:45 AM

Hi,

These site should explain more about arrays

http://www.cpearson.com/excel/array.htm

http://office.microsoft.com/en-us/ex...872921033.aspx

VBA Noob

**Zrx** · 11-26-2006, 05:27 PM

Thanks VBA Noob great reading.

**nralph13** · 11-29-2006, 04:21 PM

Originally Posted by Special-K

"I want to count the blank cells in column D but only if the blank is 3days older than..."

This doesnt make sense. It's a blank cell, it can't be "older" than anything. It's blank.

Please explain the problem more clearly.

In column A I have the date we received a purchase rec
In column B I have the area
In column C I have the REC number
In column D I have the cost code
In column E the cells are blank until the purchase order has been processed and placed with the supplier.
What I am trying to do is count the blank cells in each worksheet (bearing in mind we have 25 sheets for all different areas)and if the are 3days older than the date of reciept of Purchase Rec then we need it flagged up by a total number in a cell on the front page.
Sorry if I didn't explain myself - please except my apologies
Thanks for your time

**daddylonglegs** · 11-29-2006, 09:24 PM

Try

=SUMPRODUCT(--(A1:A100<>""),--(A1:A100<TODAY()-2),--(E1:E100=""))

**nralph13** · 11-30-2006, 05:21 PM

Originally Posted by protonLeah

From your post, it seems that Col-A is "Order Date" and Col-D is Completion/Fill Date. Hopefully, you have a status column? For example, using 100 rows of col-G for the status, then:

=IF(AND(G1:G100="",NETWORKDAYS(A1,TODAY())>3),"Overdue", "")

and copy down the used rows of the column.
Then in G101 you can sum the rows that contain "overdue" with:

=COUNTIF(G1:G100,"overdue")

Finally, in some cell on sheet 25 you can add the G101's of the previous 24 sheets with the array formula:

=SUM(Sheet1:Sheet24!G1)

and pressing Ctrl-Shift-Enter instead of just the enter key to accept the formula into the cell.

THANKS FOR THE FORMULA,
I HAVE TRIED IT BUT IT COMES BACK WITH A MESSAGE SAYING
DON'T USE EQUALS & FULL STOPS DOES THIS MAKE A DIFFERENCE.
ALSO WHAT DOES NETWORKDAYS DO IN THE FORMULA?
ALSO THE WORD OVERDUE DOES NOT APPEAR IN THE STATUS COLUMN
THANKS FOR YOUR HELP