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.