sumproduct with duplicates in two columns

[MAANI]

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.

 

[Bob Phillips]

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

 

[MAANI]

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]

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?

 

[MAANI]

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]

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/2001PivotTableIntroduction.pdf

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

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

 

[MAANI]

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]

I am just staggered that you use Excel as the source of that much data, it
is a very flakey idea IMO. If you dropped them into a database, you could
run summarised queries (from Excel) against that database.

I am not suggesting that you change it to pivot tables, I am saying add
pivot tables to the workbook to give you a summarised view of your data.