Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.misc




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. 
#2
Posted to microsoft.public.excel.misc




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
Posted to microsoft.public.excel.misc




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
Posted to microsoft.public.excel.misc




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
Posted to microsoft.public.excel.misc




SUMIF function and cell /sheet reference

Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Can I use a Reference inside a SUMIF or COUNTIF Function?  Excel Worksheet Functions  
Using SUMIF Function with a named cell reference as value in CRITE  Excel Worksheet Functions  
indirect function within sumif to reference other sheets  Excel Worksheet Functions  
indirect function to reference cell on different sheet  Excel Worksheet Functions  
Can I use a cell reference in the criteria for the sumif function.  Excel Worksheet Functions 