Excel ignores formated rows

[crafty]

This sheet drives me nuts.
I am trying to set up a sheet, where I can pull out several information about the order performance.

I made Excel give me the number of total days the orders were proceeded, less than 5, than 10 and then all else greater than 10. There but the total didn't match the overall total of orders and there was a slight difference of 89 pcs.
I now set a filter and found out, that he wouldn't put exactly 89 pcs. in order when I sort the orders by the time they were proceeded. To me it seems, that Excel ignores them. Does anybody have any idea, what the problem could be?

Would really appreciate if anybody would take a look into this big thing. I really can't figure it out!

This is part of the table I'm trying to set up. You'll find the deviating numbers on the top left and right. The formulars are below.

	G	I	J	L	M	R	S	T	U	V | W | X | Y |
1		by quantity	by %							Lead time whithin
2	total of orders	10896								<5 days	<10 days	>10
3	overrun delivery date	2970	27.26%							7681	2277	849	10807
4	on credit release	2497	22.92%
5										Orders overrun delivery day by
6										<5 days	<10 days	>10 days
7										2352	385	233	2970
8
9										Orders on credit block
10										<5 days	<10 days	>10 days
11										1446	74	39	1559
12
13	Order_create	Credit_block	Credit_release	Req_delivery	Goods_issue	Total days proceeded	On credit block	Days overrun
14	4/6/11 12:56 PM			4/7/2011	4/6/11 7:23 PM	1
15	5/3/11 3:29 PM			5/3/2011	5/3/11 4:04 PM	1
16	6/22/11 12:19 PM			6/22/2011	6/22/11 2:14 PM	1
17	8/12/11 2:34 PM			8/12/2011	8/12/11 3:32 PM	1
18	2/23/11 2:34 PM			2/24/2011	2/23/11 4:25 PM	1
19	5/20/11 2:26 PM			5/20/2011	5/20/11 7:04 PM	1
20	9/6/11 3:27 PM			9/12/2011	9/6/11 6:32 PM	1
21	10/14/11 3:31 PM			10/20/2011	10/14/11 4:43 PM	1
22	10/4/11 12:17 PM			10/5/2011	10/4/11 7:16 PM	1
23	12/21/11 3:25 PM			12/21/2011	12/21/11 6:04 PM	1
24	8/26/11 5:47 PM			9/2/2011	8/26/11 6:34 PM	1
25	9/16/11 2:34 PM			9/26/2011	9/16/11 5:39 PM	1
26	2/22/11 10:37 AM	2/22/11 10:37 AM	2/22/11 10:55 AM	2/28/2011		371	1
27	2/3/11 12:11 PM			2/3/2011	2/3/11 1:19 PM	1
28	5/12/11 12:42 PM			5/13/2011	5/12/11 5:42 PM	1

<tbody>
</tbody>

Formulas used as follows:
I2: =COUNTA($R14:$R30000)
I3: =COUNT($T14:$T30011)
I4: =COUNT($J14:$J30011)

V3: =COUNTIF($R14:$R30000,"<=5")
W3: =(COUNTIF($R14:$R30000,"<=10"))-V3
X3: =COUNTIF($R14:$R30007,">10")
Y3: =SUM(V3:X3)

V7: =COUNTIF($T14:$T30011,"<=5")
W7: =(COUNTIF($T14:$T30011,"<=10"))-$V$7
X7: =(COUNTIF($T14:$T30011,">10"))
Y7: =SUM(V7:X7)

Same for the credit one.


Then for the matrix itself as follows:

R14: =IF((IF((HOUR(G14)<12),(NETWORKDAYS(G14,(IF(M14="",TODAY(),M14)))),(NETWORKDAYS((G14+1),(IF(M14="",TODAY(),M14))))))<=0,"1",(IF((HOUR(G14)<12),(NETWORKDAYS(G14,(IF(M14="",TODAY(),M14)))),(NETWORKDAYS((G14+1),(IF(M14="",TODAY(),M14)))))))
S14: =IF((IF(I14<>"",(IF(HOUR(MAX(I14,J14))<12,NETWORKDAYS(MAX(I14),J14),NETWORKDAYS(MAX(I14)+1,J14))),""))<=0,"1",(IF(I14<>"",(IF(HOUR(MAX(I14,J14))<12,NETWORKDAYS(MAX(I14),J14),NETWORKDAYS(MAX(I14)+1,J14))),"")))
T14: =IF(($L14+1)>$M14,"",(NETWORKDAYS($L14,$M14)))


Thanks so much for any advice!

 

[Anthony47]

I got lost trying connecting your table with your description.
However I wonder if the misaligment in the range used (sometimes rows 14:30000, sometimes 14:30007, sometimes 14:30011) could justify your wrong results.

Bye

 

[JLGWhiz]

I think maybe your formula for I two using CountA is counting cells with formulas in them when their value is actually blank. But that is just a guess.

 

[crafty]

@Anthony47: I bet it's confusing.
The tabel J1:G4 is really just to get a quick overview about the total amount of orders. V1:Y11 also, but they are supposed to show the total orders more detailed. Those tabels refer to the orders, which are listed below from row 14 to 10909, which equals a total of 10896 rows in use and which is definitely correct. The orders dont't match the analysis tabels above though.
The varying numbers you mentioned shouldn't be the problem, since I'm really only have roughly 11,000 rows in use and the formulars ignore empty cells.

@JLGWhiz: I changed it to a regular >count< formula and now it shows the same result as on the leadtime calculation. This shouldn't be right tough! I am 100% sure, that there are 10896 orders. Excel is definitely, however, ignoring 89 rows/orders and not counting them.

Any other ideas?

 

[Anthony47]

I don't like that formula in R14 might return "1" (string with character 1, not the value 1); try replacing that "1" with plain 1 (no "quotes").
I mean, NOT
R14: =IF((IF((HOUR(G14)<12),(NETWORKDAYS(G14,(IF(M14="",TODAY(),M14)))),(NETWORKDAYS((G14+1),(IF(M14="",TODAY(),M14))))))<=0,"1",(IF((HOUR(G14)<12),(NETWORKDAYS(G14,(IF(M14="",TODAY(),M14)))),(NETWORKDAYS((G14+1),(IF(M14="",TODAY(),M14)))))))

BUT
R14: =IF((IF((HOUR(G14)<12),(NETWORKDAYS(G14,(IF(M14="",TODAY(),M14)))),(NETWORKDAYS((G14+1),(IF(M14="",TODAY(),M14))))))<=0,1,(IF((HOUR(G14)<12),(NETWORKDAYS(G14,(IF(M14="",TODAY(),M14)))),(NETWORKDAYS((G14+1),(IF(M14="",TODAY(),M14)))))))

Indeed this should have caused a large misalignment with the results, but it has to be cleaned.

Bye

 

[crafty]

All the data's are now as how they are supposed to be and the number are all correct now.

Thank's so much for taking your time!