If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




SUMIF function and cell /sheet reference
Dear Excel Experts,
I have the following tables on my sheet 11 Mar 09 SUP1 $30 1 Apr 09 SUP1 $50 2 Apr 09 SUP3 $10 4 Apr 09 SUP2 $20 16 Apr 09 SUP1 $100 I have 2 general excel questions: 1. How do I use SUMIF function using 2 criterias? For an example, I want to SUM the amount that is purchased from Supplier 1 in April. Or is there another formula that accomodate this? 2. In excel, you have 2 sheets called Apple and Orange. In Apple sheet, a cell A1 is referenced to A1's Orange sheet. Dragging A1 down, will autofill the rest of the coloum referencing Orange Sheet. Now, if I have 10 sheets (Sheet1Sheet10). On Sheet1  A1 cell, I reffence Sheet2A1. (=Sheet2!A1) How do autofill automatically, so that it changes betwwen Sheets, and not the cell. Result Sheet1  A1 Cell refference to Sheet2 A1 Cell Sheet1  B1 Cell refference to Sheet3 A1 Cell Sheet1  C1 Cell refference to Sheet4 A1 Cell Sheet1  D1 Cell refference to Sheet5 A1 Cell or Sheet1  A1 Cell refference to Sheet2 A1 Cell Sheet1  A2 Cell refference to Sheet3 A1 Cell Sheet1  A3 Cell refference to Sheet4 A1 Cell Sheet1  A4 Cell refference to Sheet5 A1 Cell Thanks a bunch for the help. 
Ads 
#2




SUMIF function and cell /sheet reference
in xl2007, you can use =sumifs() (read excel's help for more info).
In any version, you can use: If A1:A10 contains text (not dates): =sumproduct((b1:b10="sup1"),(a1:a10="apr 09"),(c1:c10)) If column A contained real dates: =sumproduct((b1:b10="sup1"),(text(a1:a10,"yyyymm")="200904"),(c1:c10)) Adjust the ranges to matchbut you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The  stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html ====== As long as you're using those names, put this in A1: =indirect("sheet"&row()+1&"!a1") and drag down. apache007 wrote: > > Dear Excel Experts, > > I have the following tables on my sheet > > 11 Mar 09 SUP1 $30 > 1 Apr 09 SUP1 $50 > 2 Apr 09 SUP3 $10 > 4 Apr 09 SUP2 $20 > 16 Apr 09 SUP1 $100 > > I have 2 general excel questions: > > 1. How do I use SUMIF function using 2 criterias? For an example, I want to > SUM the amount that is purchased from Supplier 1 in April. Or is there > another formula that accomodate this? > > 2. In excel, you have 2 sheets called Apple and Orange. > In Apple sheet, a cell A1 is referenced to A1's Orange sheet. > Dragging A1 down, will autofill the rest of the coloum referencing > Orange Sheet. > > Now, if I have 10 sheets (Sheet1Sheet10). > On Sheet1  A1 cell, I reffence Sheet2A1. (=Sheet2!A1) > > How do autofill automatically, so that it changes betwwen Sheets, and not > the cell. > > Result > Sheet1  A1 Cell refference to Sheet2 A1 Cell > Sheet1  B1 Cell refference to Sheet3 A1 Cell > Sheet1  C1 Cell refference to Sheet4 A1 Cell > Sheet1  D1 Cell refference to Sheet5 A1 Cell > > or > > Sheet1  A1 Cell refference to Sheet2 A1 Cell > Sheet1  A2 Cell refference to Sheet3 A1 Cell > Sheet1  A3 Cell refference to Sheet4 A1 Cell > Sheet1  A4 Cell refference to Sheet5 A1 Cell > > Thanks a bunch for the help.  Dave Peterson 
#3




SUMIF function and cell /sheet reference
Dave,
Thank you for the advice. Your solution on question #2 works good. However, what if the sheets are not Sheet1Sheet10, rather JanuaryDesember. How do I edit the formula to get the result as followed: January  A5 Cell refference to February X10 Cell January  B5 Cell refference to March X10 Cell January  C5 Cell refference to April X10 Cell January  D5 Cell refference to May X10 Cell Thank you. "Dave Peterson" wrote: > in xl2007, you can use =sumifs() (read excel's help for more info). > > In any version, you can use: > > If A1:A10 contains text (not dates): > =sumproduct((b1:b10="sup1"),(a1:a10="apr 09"),(c1:c10)) > > If column A contained real dates: > =sumproduct((b1:b10="sup1"),(text(a1:a10,"yyyymm")="200904"),(c1:c10)) > > Adjust the ranges to matchbut you can't use whole columns (except in xl2007). > > =sumproduct() likes to work with numbers. The  stuff changes trues and falses > to 1's and 0's. > > Bob Phillips explains =sumproduct() in much more detail he > http://www.xldynamic.com/source/xld.SUMPRODUCT.html > > And J.E. McGimpsey has some notes at: > http://mcgimpsey.com/excel/formulae/doubleneg.html > > ====== > As long as you're using those names, put this in A1: > =indirect("sheet"&row()+1&"!a1") > and drag down. > > > > apache007 wrote: > > > > Dear Excel Experts, > > > > I have the following tables on my sheet > > > > 11 Mar 09 SUP1 $30 > > 1 Apr 09 SUP1 $50 > > 2 Apr 09 SUP3 $10 > > 4 Apr 09 SUP2 $20 > > 16 Apr 09 SUP1 $100 > > > > I have 2 general excel questions: > > > > 1. How do I use SUMIF function using 2 criterias? For an example, I want to > > SUM the amount that is purchased from Supplier 1 in April. Or is there > > another formula that accomodate this? > > > > 2. In excel, you have 2 sheets called Apple and Orange. > > In Apple sheet, a cell A1 is referenced to A1's Orange sheet. > > Dragging A1 down, will autofill the rest of the coloum referencing > > Orange Sheet. > > > > Now, if I have 10 sheets (Sheet1Sheet10). > > On Sheet1  A1 cell, I reffence Sheet2A1. (=Sheet2!A1) > > > > How do autofill automatically, so that it changes betwwen Sheets, and not > > the cell. > > > > Result > > Sheet1  A1 Cell refference to Sheet2 A1 Cell > > Sheet1  B1 Cell refference to Sheet3 A1 Cell > > Sheet1  C1 Cell refference to Sheet4 A1 Cell > > Sheet1  D1 Cell refference to Sheet5 A1 Cell > > > > or > > > > Sheet1  A1 Cell refference to Sheet2 A1 Cell > > Sheet1  A2 Cell refference to Sheet3 A1 Cell > > Sheet1  A3 Cell refference to Sheet4 A1 Cell > > Sheet1  A4 Cell refference to Sheet5 A1 Cell > > > > Thanks a bunch for the help. > >  > > Dave Peterson > 
#4




SUMIF function and cell /sheet reference
I'd fill row 1 with the names of the sheets.
If you're really using months, then you should be able to type January and autofill the rest (drag the fill handle at the bottom right corner across the rest of the 11 columns). Then you could use: =indirect("'" & a$1 & "'!x10") and drag across apache007 wrote: > > Dave, > > Thank you for the advice. Your solution on question #2 works good. However, > what if the sheets are not Sheet1Sheet10, rather JanuaryDesember. How do I > edit the formula to get the result as followed: > > January  A5 Cell refference to February X10 Cell > January  B5 Cell refference to March X10 Cell > January  C5 Cell refference to April X10 Cell > January  D5 Cell refference to May X10 Cell > > Thank you. > > "Dave Peterson" wrote: > > > in xl2007, you can use =sumifs() (read excel's help for more info). > > > > In any version, you can use: > > > > If A1:A10 contains text (not dates): > > =sumproduct((b1:b10="sup1"),(a1:a10="apr 09"),(c1:c10)) > > > > If column A contained real dates: > > =sumproduct((b1:b10="sup1"),(text(a1:a10,"yyyymm")="200904"),(c1:c10)) > > > > Adjust the ranges to matchbut you can't use whole columns (except in xl2007). > > > > =sumproduct() likes to work with numbers. The  stuff changes trues and falses > > to 1's and 0's. > > > > Bob Phillips explains =sumproduct() in much more detail he > > http://www.xldynamic.com/source/xld.SUMPRODUCT.html > > > > And J.E. McGimpsey has some notes at: > > http://mcgimpsey.com/excel/formulae/doubleneg.html > > > > ====== > > As long as you're using those names, put this in A1: > > =indirect("sheet"&row()+1&"!a1") > > and drag down. > > > > > > > > apache007 wrote: > > > > > > Dear Excel Experts, > > > > > > I have the following tables on my sheet > > > > > > 11 Mar 09 SUP1 $30 > > > 1 Apr 09 SUP1 $50 > > > 2 Apr 09 SUP3 $10 > > > 4 Apr 09 SUP2 $20 > > > 16 Apr 09 SUP1 $100 > > > > > > I have 2 general excel questions: > > > > > > 1. How do I use SUMIF function using 2 criterias? For an example, I want to > > > SUM the amount that is purchased from Supplier 1 in April. Or is there > > > another formula that accomodate this? > > > > > > 2. In excel, you have 2 sheets called Apple and Orange. > > > In Apple sheet, a cell A1 is referenced to A1's Orange sheet. > > > Dragging A1 down, will autofill the rest of the coloum referencing > > > Orange Sheet. > > > > > > Now, if I have 10 sheets (Sheet1Sheet10). > > > On Sheet1  A1 cell, I reffence Sheet2A1. (=Sheet2!A1) > > > > > > How do autofill automatically, so that it changes betwwen Sheets, and not > > > the cell. > > > > > > Result > > > Sheet1  A1 Cell refference to Sheet2 A1 Cell > > > Sheet1  B1 Cell refference to Sheet3 A1 Cell > > > Sheet1  C1 Cell refference to Sheet4 A1 Cell > > > Sheet1  D1 Cell refference to Sheet5 A1 Cell > > > > > > or > > > > > > Sheet1  A1 Cell refference to Sheet2 A1 Cell > > > Sheet1  A2 Cell refference to Sheet3 A1 Cell > > > Sheet1  A3 Cell refference to Sheet4 A1 Cell > > > Sheet1  A4 Cell refference to Sheet5 A1 Cell > > > > > > Thanks a bunch for the help. > > > >  > > > > Dave Peterson > >  Dave Peterson 
#5




SUMIF function and cell /sheet reference

Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Can I use a Reference inside a SUMIF or COUNTIF Function?  Albie  Excel Worksheet Functions  11  May 14th 10 04:26 PM 
Using SUMIF Function with a named cell reference as value in CRITE  smilingindigo  Excel Worksheet Functions  3  June 29th 06 03:29 AM 
indirect function within sumif to reference other sheets  [email protected]  Excel Worksheet Functions  3  June 15th 06 05:46 PM 
indirect function to reference cell on different sheet  Dolemite  Excel Worksheet Functions  2  August 19th 05 05:25 PM 
Can I use a cell reference in the criteria for the sumif function.  Number Cruncher  Excel Worksheet Functions  2  November 4th 04 07:52 PM 