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.

This formula Works, if I just want to Count

=SUMPRODUCT(COUNTIF(INDIRECT("'"&A1:A36&"'!a1:a20" ),D9))

but I can't get it to work when I try to use SUMIF in stead of COUNTIF. No matter what I try to put in the last argument it doesn't Work. Either it returns an error or a zero, like when i try with

=SUMPRODUCT(SUMIF(INDIRECT("'"&A1:A36&"'!a1:a20"), D9,INDIRECT("'"&A1:A36&"'!q1:q20",)))


Any ideas?

Jan

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

Den søndag den 5. oktober 2014 13.29.00 UTC+2 skrev Claus Busch:
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




I tried your formula but get a #NAME! error.

Jan

Hi Jan,

"Jan Kronsell" schrieb

=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

I tried your formula but get a #NAME! error.

which version/language of Excel do you use?

--
Lisa

Den søndag den 5. oktober 2014 17.36.15 UTC+2 skrev Lisa Wilke-Thissen:
Hi Jan,



"Jan Kronsell" schrieb



=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



I tried your formula but get a #NAME! error.



which version/language of Excel do you use?



--

Lisa

2007

Jan

Hi Jan,

Am Sun, 5 Oct 2014 06:30:35 -0700 (PDT) schrieb Jan Kronsell:

I tried your formula but get a #NAME! error.

please look he
https://onedrive.live.com/?cid=9378A...121822A3%21326
for "SumIf3D"


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

Den søndag den 5. oktober 2014 18.23.50 UTC+2 skrev Claus Busch:
Hi Jan,



Am Sun, 5 Oct 2014 06:30:35 -0700 (PDT) schrieb Jan Kronsell:



I tried your formula but get a #NAME! error.



please look he

https://onedrive.live.com/?cid=9378A...121822A3%21326

for "SumIf3D"





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional

Thank you very much. I had made a spelling error in the translation. It works perfectly now.

Jan