I am sure someone could abbreviate this lengthy formula for me and I'd be
appreciative, but my real problem is more perplexing to me. Here is the
formula:
=SUMPRODUCT(--('[FILE.xls]Sheet1'!$I$2:$I$1800=10),'[FILE.xls]Sheet1'!$U$2:$U$1800)
+SUMPRODUCT(--('[FILE.xls]Sheet2'!$I$2:$I$1800=10),'[FILE.xls]Sheet2'!$U$2:$U$1800)
+SUMPRODUCT(--('[FILE.xls]Sheet3'!$I$2:$I$1800=10),'[FILE.xls]Sheet3'!$U$2:$U$1800)
+SUMPRODUCT(--('[FILE.xls]Sheet4'!$I$2:$I$1800=10),'[FILE.xls]Sheet4'!$U$2:$U$1800)
+SUMPRODUCT(--('[FILE.xls]Sheet5'!$I$2:$I$1800=10),'[FILE.xls]Sheet5'!$U$2:$U$1800)
+SUMPRODUCT(--('[FILE.xls]Sheet6'!$I$2:$I$1800=10),'[FILE.xls]Sheet6'!$U$2:$U$1800)
The =10 refers to an office number, and the above gets me the information I
need when the offices are numeric (10-19), but now they have offices 1A, 1B
and 1C, etc as well and the above does not work. I do not control the input
or format of FILE.xls. Any suggestions are welcome.
Bookmarks