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.