Hi Jan,

Am Sun, 5 Oct 2014 04:02:10 -0700 (PDT) schrieb Jan Kronsell:

In a workbook I have 36 sheets, one for each month from January 2012 to December 2014, named Ja12, Fe12, Ma12...No14, De14.

All sheets are alike. In Column A (A5:A20) I have customer numbers, In column Q (Q5:Q20) I have sales totals.

In an additional sheet, I like to be able to type a customer number in D9, and in F9 have returned the total sale for te customer over the 36 months. In this sheet, column A I have a list of all my 36 sheets.

write in this additional sheet from A1 to A36 all the sheet names.
If your customer number is a real number then try:
=SUM((N(INDIRECT("'"&TRANSPOSE(A1:A36)&"'!A"&ROW(5 :20)))=D9)*(N(INDIRECT("'"&TRANSPOSE(A1:A36)&"'!Q" &ROW(5:20)))))
If the customer number is text then try:
=SUM((T(INDIRECT("'"&TRANSPOSE(A1:A36)&"'!A"&ROW(5 :20)))=D9)*(N(INDIRECT("'"&TRANSPOSE(A1:A36)&"'!Q" &ROW(5:20)))))

Both formulas are array formulas to insert with CTRL+Shift+Enter


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional