# Sum of total from other worksheet with that meets one criteria

Excel Sum function with IF
Guest
Posts: n/a

 16th Mar 2009
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
Guest
Posts: n/a

 16th Mar 2009
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
(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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?

Jacob Skaria
Guest
Posts: n/a

 16th Mar 2009
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

Excel Sum function with IF
Guest
Posts: n/a

 16th Mar 2009
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
> (E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >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
Guest
Posts: n/a

 16th Mar 2009
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
> > (E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > >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?

> >
> >
> >

Jacob Skaria
Guest
Posts: n/a

 16th Mar 2009
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

Shane Devenshire
Guest
Posts: n/a

 16th Mar 2009
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
> > (E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > >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?

> >
> >
> >

Dave Peterson
Guest
Posts: n/a

 16th Mar 2009
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

HABIB
Guest
Posts: n/a

 25th May 2009
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
Guest
Posts: n/a

 25th May 2009
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

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post JudyK Microsoft Excel Misc 1 9th Apr 2010 05:35 PM =?Utf-8?B?ZzRyb2Q=?= Microsoft Excel Worksheet Functions 3 3rd Oct 2006 05:10 PM terrapinie Microsoft Excel Discussion 5 31st Mar 2005 06:49 PM halem2 Microsoft Excel Worksheet Functions 6 8th Jun 2004 01:35 PM Aileen Microsoft Access Forms 1 26th Sep 2003 07:49 PM

Features