Remember Me?

#1
April 29th 09, 02:19 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Apr 2009 Posts: 36
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 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.

#2
April 29th 09, 03:29 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 35,218
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
#3
April 29th 09, 05:32 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Apr 2009 Posts: 36
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 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

#4
April 29th 09, 12:37 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 35,218
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 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

--

Dave Peterson
 #5     January 3rd 17, 07:51 AM posted to microsoft.public.excel.misc SUMIF function and cell /sheet reference

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Albie Excel Worksheet Functions 11 May 14th 10 04:26 PM smilingindigo Excel Worksheet Functions 3 June 29th 06 03:29 AM [email protected] Excel Worksheet Functions 3 June 15th 06 05:46 PM Dolemite Excel Worksheet Functions 2 August 19th 05 05:25 PM Number Cruncher Excel Worksheet Functions 2 November 4th 04 07:52 PM

All times are GMT +1. The time now is 09:46 AM.