Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default SUMIF across multiple sheets

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default SUMIF across multiple sheets

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
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default SUMIF across multiple sheets

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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default SUMIF across multiple sheets

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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default SUMIF across multiple sheets

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


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default SUMIF across multiple sheets

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
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default SUMIF across multiple sheets

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumif across Multiple sheets beenlosteverywhere Excel Worksheet Functions 2 January 13th 09 02:36 AM
sumif over multiple sheets Fritzi Excel Discussion (Misc queries) 3 October 23rd 08 06:10 PM
SUMPRODUCT, SUMIF, COUNTIF for multiple sheets for multiple criter Greg in CO[_2_] Excel Worksheet Functions 0 September 18th 08 05:51 PM
SUMIF with multiple sheets frustratedwthis Excel Discussion (Misc queries) 5 May 19th 06 11:54 PM
SUMIF on multiple sheets JJackson Excel Worksheet Functions 5 December 28th 05 12:43 AM


All times are GMT +1. The time now is 03:20 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"