SUMIF function and cell /sheet reference

[apache007]

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]

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 here:
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]

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]

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