Sum of total from other worksheet with that meets one criteria

Discussion in 'Microsoft Excel Worksheet Functions' started by Excel Sum function with IF, Mar 16, 2009.

  1. I am trying to figure out how to add all dollars amounts in other worksheet
    under same team ID...for instance, there are 100 different team ID but there
    are some same Team ID. I want to add all values under the same Team ID. I
    don't want to sort the Team ID then do sum every time. I want to be able to
    type in data without having to sort it and update the function in other
    sheet. How do I do that?
     
    Excel Sum function with IF, Mar 16, 2009
    #1
    1. Advertisements

  2. Excel Sum function with IF

    T. Valko Guest

    Try this:

    =SUMIF(A1:A100,"team ID",B1:B100)

    Better to use a cell to hold the criteria:

    D1 = team ID

    =SUMIF(A1:A100,D1,B1:B100)

    --
    Biff
    Microsoft Excel MVP


    "Excel Sum function with IF" <Excel Sum function with
    > wrote in message
    news:...
    >I am trying to figure out how to add all dollars amounts in other worksheet
    > under same team ID...for instance, there are 100 different team ID but
    > there
    > are some same Team ID. I want to add all values under the same Team ID.
    > I
    > don't want to sort the Team ID then do sum every time. I want to be able
    > to
    > type in data without having to sort it and update the function in other
    > sheet. How do I do that?
     
    T. Valko, Mar 16, 2009
    #2
    1. Advertisements

  3. Excel Sum function with IF

    Jacob Skaria Guest

    Hi Dear

    Try this

    =SUMIF(A1:A10,"=TeamA",B1:B10)

    Column A has Team Names
    Column B has amounts

    If this post helps click Yes
    ---------------
    Jacob Skaria
     
    Jacob Skaria, Mar 16, 2009
    #3
  4. I mean...I want to add all values (in column C) in other worksheet under
    specific Team ID in Column A into other worksheet

    I think there is sumif involved like sum of values under column C only if
    column A equals 3 (team id) then put result in other sheet.

    "T. Valko" wrote:

    > Try this:
    >
    > =SUMIF(A1:A100,"team ID",B1:B100)
    >
    > Better to use a cell to hold the criteria:
    >
    > D1 = team ID
    >
    > =SUMIF(A1:A100,D1,B1:B100)
    >
    > --
    > Biff
    > Microsoft Excel MVP
    >
    >
    > "Excel Sum function with IF" <Excel Sum function with
    > > wrote in message
    > news:...
    > >I am trying to figure out how to add all dollars amounts in other worksheet
    > > under same team ID...for instance, there are 100 different team ID but
    > > there
    > > are some same Team ID. I want to add all values under the same Team ID.
    > > I
    > > don't want to sort the Team ID then do sum every time. I want to be able
    > > to
    > > type in data without having to sort it and update the function in other
    > > sheet. How do I do that?

    >
    >
    >
     
    Excel Sum function with IF, Mar 16, 2009
    #4
  5. Excel Sum function with IF

    Eduardo Guest

    Hy,
    try
    =sumproduct(--(A1=Sheet2!A:A),--Sheet2!C:C)

    I assumed your summary is in sheet 1 team information starting in A1 and the
    information you want to summarize is in sheet2

    "Excel Sum function with IF" wrote:

    > I mean...I want to add all values (in column C) in other worksheet under
    > specific Team ID in Column A into other worksheet
    >
    > I think there is sumif involved like sum of values under column C only if
    > column A equals 3 (team id) then put result in other sheet.
    >
    > "T. Valko" wrote:
    >
    > > Try this:
    > >
    > > =SUMIF(A1:A100,"team ID",B1:B100)
    > >
    > > Better to use a cell to hold the criteria:
    > >
    > > D1 = team ID
    > >
    > > =SUMIF(A1:A100,D1,B1:B100)
    > >
    > > --
    > > Biff
    > > Microsoft Excel MVP
    > >
    > >
    > > "Excel Sum function with IF" <Excel Sum function with
    > > > wrote in message
    > > news:...
    > > >I am trying to figure out how to add all dollars amounts in other worksheet
    > > > under same team ID...for instance, there are 100 different team ID but
    > > > there
    > > > are some same Team ID. I want to add all values under the same Team ID.
    > > > I
    > > > don't want to sort the Team ID then do sum every time. I want to be able
    > > > to
    > > > type in data without having to sort it and update the function in other
    > > > sheet. How do I do that?

    > >
    > >
    > >
     
    Eduardo, Mar 16, 2009
    #5
  6. Excel Sum function with IF

    Jacob Skaria Guest

    Try this

    =SUMIF([Book1]Sheet1!A1:A10,"=TeamA",[Book1]Sheet1!B1:B10)

    Column A has Team Names
    Column B has amounts

    If this post helps click Yes
    ---------------
    Jacob Skaria
     
    Jacob Skaria, Mar 16, 2009
    #6
  7. Hi,

    nothing wrong with Biff suggestion, just change the references

    =SUMIF(Sheet2!A1:A100,"team ID",Sheet2!C1:C100)

    if the team id is in A1 of Sheet1 then

    =SUMIF(Sheet2!A1:A100,A1,Sheet2!C1:C100)

    --
    If this helps, please click the Yes button.

    Cheers,
    Shane Devenshire


    "Excel Sum function with IF" wrote:

    > I mean...I want to add all values (in column C) in other worksheet under
    > specific Team ID in Column A into other worksheet
    >
    > I think there is sumif involved like sum of values under column C only if
    > column A equals 3 (team id) then put result in other sheet.
    >
    > "T. Valko" wrote:
    >
    > > Try this:
    > >
    > > =SUMIF(A1:A100,"team ID",B1:B100)
    > >
    > > Better to use a cell to hold the criteria:
    > >
    > > D1 = team ID
    > >
    > > =SUMIF(A1:A100,D1,B1:B100)
    > >
    > > --
    > > Biff
    > > Microsoft Excel MVP
    > >
    > >
    > > "Excel Sum function with IF" <Excel Sum function with
    > > > wrote in message
    > > news:...
    > > >I am trying to figure out how to add all dollars amounts in other worksheet
    > > > under same team ID...for instance, there are 100 different team ID but
    > > > there
    > > > are some same Team ID. I want to add all values under the same Team ID.
    > > > I
    > > > don't want to sort the Team ID then do sum every time. I want to be able
    > > > to
    > > > type in data without having to sort it and update the function in other
    > > > sheet. How do I do that?

    > >
    > >
    > >
     
    Shane Devenshire, Mar 16, 2009
    #7
  8. If that sending workbook (book1) is closed, then this formula will return an
    error.

    Jacob Skaria wrote:
    >
    > Try this
    >
    > =SUMIF([Book1]Sheet1!A1:A10,"=TeamA",[Book1]Sheet1!B1:B10)
    >
    > Column A has Team Names
    > Column B has amounts
    >
    > If this post helps click Yes
    > ---------------
    > Jacob Skaria


    --

    Dave Peterson
     
    Dave Peterson, Mar 16, 2009
    #8
  9. Excel Sum function with IF

    HABIB Guest

    RE: Sum of total with that meets two criteria

    Hi Dear specialist,

    Kindly help me for "SUMIF" whenI have as below ;

    Col A : Name
    Col B : Name OR Num
    Col C : Amount

    I need to get sum of total from Col C if Cols "A" and "B" are combined?
    Thanks in advance

    -----------
    HABIB



    "Jacob Skaria" wrote:

    > Hi Dear
    >
    > Try this
    >
    > =SUMIF(A1:A10,"=TeamA",B1:B10)
    >
    > Column A has Team Names
    > Column B has amounts
    >
    > If this post helps click Yes
    > ---------------
    > Jacob Skaria
     
    HABIB, May 25, 2009
    #9
  10. Excel Sum function with IF

    HABIB Guest

    RE: Sum of total with that meets two criteria

    Many thanks Dear,

    I meant when i want to refer both of criteria of Col A and col B for example;

    Col A : Profit and ...
    Col B : 2008 and ....
    Col C : profit in 2008 "14000 $" and....

    Now needs to get SUMIF(A1:B10,"Profit"and"2008",C1:C10)
    refer to 14000$
    can we use it as above?

    look forward to
    Habib

    "HABIB" wrote:

    > Hi Dear specialist,
    >
    > Kindly help me for "SUMIF" whenI have as below ;
    >
    > Col A : Name
    > Col B : Name OR Num
    > Col C : Amount
    >
    > I need to get sum of total from Col C if Cols "A" and "B" are combined?
    > Thanks in advance
    >
    > -----------
    > HABIB
    >
    >
    >
    > "Jacob Skaria" wrote:
    >
    > > Hi Dear
    > >
    > > Try this
    > >
    > > =SUMIF(A1:A10,"=TeamA",B1:B10)
    > >
    > > Column A has Team Names
    > > Column B has amounts
    > >
    > > If this post helps click Yes
    > > ---------------
    > > Jacob Skaria
     
    HABIB, May 25, 2009
    #10
  11. Excel Sum function with IF

    T. Valko Guest

    Re: Sum of total with that meets two criteria

    My best guess:

    =SUMPRODUCT(--(A1:A10="Profit"),--(B1:B10=2008),C1:C10)

    --
    Biff
    Microsoft Excel MVP


    "HABIB" <> wrote in message
    news:...
    > Many thanks Dear,
    >
    > I meant when i want to refer both of criteria of Col A and col B for
    > example;
    >
    > Col A : Profit and ...
    > Col B : 2008 and ....
    > Col C : profit in 2008 "14000 $" and....
    >
    > Now needs to get SUMIF(A1:B10,"Profit"and"2008",C1:C10)
    > refer to 14000$
    > can we use it as above?
    >
    > look forward to
    > Habib
    >
    > "HABIB" wrote:
    >
    >> Hi Dear specialist,
    >>
    >> Kindly help me for "SUMIF" whenI have as below ;
    >>
    >> Col A : Name
    >> Col B : Name OR Num
    >> Col C : Amount
    >>
    >> I need to get sum of total from Col C if Cols "A" and "B" are combined?
    >> Thanks in advance
    >>
    >> -----------
    >> HABIB
    >>
    >>
    >>
    >> "Jacob Skaria" wrote:
    >>
    >> > Hi Dear
    >> >
    >> > Try this
    >> >
    >> > =SUMIF(A1:A10,"=TeamA",B1:B10)
    >> >
    >> > Column A has Team Names
    >> > Column B has amounts
    >> >
    >> > If this post helps click Yes
    >> > ---------------
    >> > Jacob Skaria
     
    T. Valko, May 25, 2009
    #11
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Maureen

    MORE THAN ONE RECORD MEETS THE CRITERIA

    Maureen, Dec 23, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    183
  2. halem2

    Sum If Meets Criteria

    halem2, Jun 7, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    6
    Views:
    170
    halem2
    Jun 8, 2004
  3. Guest

    sum every other cell in a row if next cell meets certain criteria

    Guest, Oct 3, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    198
    Bob Phillips
    Oct 3, 2006
  4. P0llyW0G

    Populate worksheet with data that meets date range criteria

    P0llyW0G, Sep 27, 2008, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    201
    P0llyW0G
    Sep 28, 2008
  5. ccKennedy

    count entries that equal one criteria if another column meets anot

    ccKennedy, Jan 24, 2009, in forum: Microsoft Excel Worksheet Functions
    Replies:
    7
    Views:
    1,206
    Shane Devenshire
    Jan 24, 2009
Loading...

Share This Page