View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson Dave Peterson is offline
external usenet poster
 
Posts: 35,218
Default 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 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