Dave,
Thank you for the advice. Your solution on question #2 works good. However,
what if the sheets are not Sheet1-Sheet10, rather January-Desember. 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 match--but 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 auto-fill the rest of the coloum referencing
Orange Sheet.
Now, if I have 10 sheets (Sheet1-Sheet10).
On Sheet1 - A1 cell, I reffence Sheet2-A1. (=Sheet2!A1)
How do auto-fill 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