PC Review


Reply
Thread Tools Rate Thread

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?
 
Reply With Quote
 
 
 
 
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
http://www.pcreview.co.uk/forums/(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?



 
Reply With Quote
 
 
 
 
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
 
Reply With Quote
 
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?

>
>
>

 
Reply With Quote
 
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?

> >
> >
> >

 
Reply With Quote
 
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

 
Reply With Quote
 
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?

> >
> >
> >

 
Reply With Quote
 
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
 
Reply With Quote
 
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?
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

 
Reply With Quote
 
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?
> 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

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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 Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
if cell in other worksheet meets criteria, then leave blank. JudyK Microsoft Excel Misc 1 9th Apr 2010 05:35 PM
sum every other cell in a row if next cell meets certain criteria =?Utf-8?B?ZzRyb2Q=?= Microsoft Excel Worksheet Functions 3 3rd Oct 2006 05:10 PM
Sum a column that meets two criteria terrapinie Microsoft Excel Discussion 5 31st Mar 2005 06:49 PM
Sum If Meets Criteria halem2 Microsoft Excel Worksheet Functions 6 8th Jun 2004 01:35 PM
HELP! Change color of a control if criteria meets criteria in an unbound box Aileen Microsoft Access Forms 1 26th Sep 2003 07:49 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 04:13 AM.