# 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. ### Excel Sum function with IFGuest

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

2. ### T. ValkoGuest

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

3. ### Jacob SkariaGuest

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
4. ### Excel Sum function with IFGuest

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
5. ### EduardoGuest

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
6. ### Jacob SkariaGuest

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
7. ### Shane DevenshireGuest

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
8. ### Dave PetersonGuest

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
9. ### HABIBGuest

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?

-----------
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. ### HABIBGuest

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?
>
> -----------
> 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
11. ### T. ValkoGuest

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?
>>
>> -----------
>> 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