A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

SUMIF function and cell /sheet reference



 
 
Thread Tools Display Modes
  #1  
Old April 29th 09, 02:19 AM posted to microsoft.public.excel.misc
apache007
external usenet poster
 
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.
Ads
  #2  
Old April 29th 09, 03:29 AM posted to microsoft.public.excel.misc
Dave Peterson
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
  #3  
Old April 29th 09, 05:32 AM posted to microsoft.public.excel.misc
apache007
external usenet poster
 
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  
Old April 29th 09, 12:37 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
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
  #5  
Old January 3rd 17, 07:51 AM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default SUMIF function and cell /sheet reference

http://tutorialway.com/use-sumif-function-in-excel/
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 10:18 AM.


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