Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ISBLANK function not working when cell is blank dut to function re | Excel Discussion (Misc queries) | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions |