Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 29th 09, 02:19 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2009
Posts: 36
Default 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   Report Post  
Old 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
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
  #3   Report Post  
Old April 29th 09, 05:32 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2009
Posts: 36
Default 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   Report Post  
Old 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
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I use a Reference inside a SUMIF or COUNTIF Function? Albie Excel Worksheet Functions 11 May 14th 10 04:26 PM
Using SUMIF Function with a named cell reference as value in CRITE smilingindigo Excel Worksheet Functions 3 June 29th 06 03:29 AM
indirect function within sumif to reference other sheets [email protected] Excel Worksheet Functions 3 June 15th 06 05:46 PM
indirect function to reference cell on different sheet Dolemite Excel Worksheet Functions 2 August 19th 05 05:25 PM
Can I use a cell reference in the criteria for the sumif function. Number Cruncher Excel Worksheet Functions 2 November 4th 04 07:52 PM


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

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017