#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Sum If function

I have two sheets, sheet1 and sheet2, Sheet1 contain all data and it has
three columns Name1, Name2 and Amount.

Sheet2 have two columns Name and Amount, so i need a sumif funtion to pull
the amount column from sheet1 to sheet2 as per the names which ever i type in
sheet2 name column

Example:
Sheet1:
Name1 Name2 Amount
a p 10
b q 20
a r 30

Sheet2:
Name Amount
a 40 [=SUMIF(Sheet1!A1:A4,Sheet2!A2,Sheet1!C1:C4)]

suppose if i replace the sheet2 Name column from 'a' to 'p' the above sumif
function need to change its range automatically from Sheet1!A1:A4 to
Sheet1!B1:B4
and the amount column should show the answer as 10.


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,646
Default Sum If function

=SUMIF(IF(IF(ISERROR(MATCH(A2,Sheet1!A:A,0)),"B"," A")="A",Sheet1!A:A,Sheet1!B:B),A2,Sheet1!C:C)

Regards,
Stefi

€˛Ranjith Kurian€¯ ezt Ć*rta:

I have two sheets, sheet1 and sheet2, Sheet1 contain all data and it has
three columns Name1, Name2 and Amount.

Sheet2 have two columns Name and Amount, so i need a sumif funtion to pull
the amount column from sheet1 to sheet2 as per the names which ever i type in
sheet2 name column

Example:
Sheet1:
Name1 Name2 Amount
a p 10
b q 20
a r 30

Sheet2:
Name Amount
a 40 [=SUMIF(Sheet1!A1:A4,Sheet2!A2,Sheet1!C1:C4)]

suppose if i replace the sheet2 Name column from 'a' to 'p' the above sumif
function need to change its range automatically from Sheet1!A1:A4 to
Sheet1!B1:B4
and the amount column should show the answer as 10.


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 48
Default Sum If function

Hi Stefi,

Thanks a lot, its working fine, but i have many columns like name1, name2
etc....
so if i apply the below fomula it will be very length, so is it possible to
use any other small function which gives the same result.



"Stefi" wrote:

=SUMIF(IF(IF(ISERROR(MATCH(A2,Sheet1!A:A,0)),"B"," A")="A",Sheet1!A:A,Sheet1!B:B),A2,Sheet1!C:C)

Regards,
Stefi

€˛Ranjith Kurian€¯ ezt Ć*rta:

I have two sheets, sheet1 and sheet2, Sheet1 contain all data and it has
three columns Name1, Name2 and Amount.

Sheet2 have two columns Name and Amount, so i need a sumif funtion to pull
the amount column from sheet1 to sheet2 as per the names which ever i type in
sheet2 name column

Example:
Sheet1:
Name1 Name2 Amount
a p 10
b q 20
a r 30

Sheet2:
Name Amount
a 40 [=SUMIF(Sheet1!A1:A4,Sheet2!A2,Sheet1!C1:C4)]

suppose if i replace the sheet2 Name column from 'a' to 'p' the above sumif
function need to change its range automatically from Sheet1!A1:A4 to
Sheet1!B1:B4
and the amount column should show the answer as 10.


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Sum If function

Maybe this:

=SUMPRODUCT((Sheet1!A2:B4=Sheet2!A2)*Sheet1!C2:C4)

--
Biff
Microsoft Excel MVP


"Ranjith Kurian" wrote in message
...
Hi Stefi,

Thanks a lot, its working fine, but i have many columns like name1, name2
etc....
so if i apply the below fomula it will be very length, so is it possible
to
use any other small function which gives the same result.



"Stefi" wrote:

=SUMIF(IF(IF(ISERROR(MATCH(A2,Sheet1!A:A,0)),"B"," A")="A",Sheet1!A:A,Sheet1!B:B),A2,Sheet1!C:C)

Regards,
Stefi

"Ranjith Kurian" ezt ķrta:

I have two sheets, sheet1 and sheet2, Sheet1 contain all data and it
has
three columns Name1, Name2 and Amount.

Sheet2 have two columns Name and Amount, so i need a sumif funtion to
pull
the amount column from sheet1 to sheet2 as per the names which ever i
type in
sheet2 name column

Example:
Sheet1:
Name1 Name2 Amount
a p 10
b q 20
a r 30

Sheet2:
Name Amount
a 40 [=SUMIF(Sheet1!A1:A4,Sheet2!A2,Sheet1!C1:C4)]

suppose if i replace the sheet2 Name column from 'a' to 'p' the above
sumif
function need to change its range automatically from Sheet1!A1:A4 to
Sheet1!B1:B4
and the amount column should show the answer as 10.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
ISBLANK function not working when cell is blank dut to function re mcmilja Excel Discussion (Misc queries) 9 May 7th 23 03:43 AM
copy of excel file not showing formulal/function in the function b oaallam Excel Discussion (Misc queries) 4 September 6th 07 01:20 PM
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) [email protected] Excel Worksheet Functions 0 September 5th 06 03:44 PM
Offset function with nested match function not finding host ss. MKunert Excel Worksheet Functions 1 March 21st 06 11:46 PM
Emulate Index/Match combo function w/ VBA custom function Spencer Hutton Excel Worksheet Functions 2 May 2nd 05 05:26 PM


All times are GMT +1. The time now is 03:45 PM.

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

About Us

"It's about Microsoft Excel"