Bob,its a daily time sheets for all employees turned to a data entry
person.so its updated daily,and I'm afraid that we can't change all that to
pivot tables now !!

"Bob Phillips" wrote:

You have a workbook circa 250,000 rows of data that you update manually?
Unbelievable!

Pivot tables are probably your best bet, check out

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

http://edferrero.m6.net/Pivot.html

http://it.fuqua.duke.edu/public/2001...troduction.pdf

http://peltiertech.com/Excel/Pivots/pivotstart.htm

http://www.contextures.com/xlPivot01.html


--
__________________________________
HTH

Bob

"MAANI" wrote in message
...
What do you mean by where the data come from ? Its a workbook thats
updated
daily manually ! I don't know how to query the source,nor pivoting it.

"Bob Phillips" wrote:

Then you have a real problem I am afraid. That formula would be
horrendously
slow in your circumstances.

Where does the data come from, could you query the source instead. Other
than that, have you tried pivoting it with multiple ranges?

--
__________________________________
HTH

Bob

"MAANI" wrote in message
...
Thanks Bob for the fast reply,but theres one problem.First let me
explain
why
its 4 sheets,this is because all 65536 rows are used.Now the problem is
that
if I have order number in two sheets but date are same,this will give
me
count of 2 days instead of 1.Example:
Sheet1
B G
7-Jan-09 678

Sheet2
B G
7-Jan-09 678

your formula will result of 2,and it should be 1.

"Bob Phillips" wrote:

Try this array formula

=SUM(--(FREQUENCY(IF(Sheet1!$G$2:$G$20=678,MATCH(Sheet1!$ B$2:$B$20,Sheet1!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet1!$B$2:$B$20))))0))
+SUM(--(FREQUENCY(IF(Sheet2!$G$2:$G$20=678,MATCH(Sheet2!$ B$2:$B$20,Sheet2!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet2!$B$2:$B$20))))0))
+SUM(--(FREQUENCY(IF(Sheet3!$G$2:$G$20=678,MATCH(Sheet3!$ B$2:$B$20,Sheet3!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet3!$B$2:$B$20))))0))
+SUM(--(FREQUENCY(IF(Sheet4!$G$2:$G$20=678,MATCH(Sheet4!$ B$2:$B$20,Sheet4!$B$2:$B$20,0)),ROW(INDIRECT("1:"& ROWS(Sheet4!$B$2:$B$20))))0))

Enter it with Ctrl-Shift-Enter, not Enter

--
__________________________________
HTH

Bob

"MAANI" wrote in message
...
I have 4 identical sheets that have same columns,different data.In
each
sheet
column B has dates,and column G has order numbers,(see example
below),I
want
to know the number of days an order took place.
B G
2-Jan-09 678
2-Jan-09 555
3-Jan-09 678
4-Jan-09 555
4-Jan-09 678
4-Jan-09 678

So number of days for order 678 is 3 and for order 555 its 2.Note
that
order
678 or 555 could appear in more than one sheet.