Sum If function

[Ranjith Kurian]

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.

 

[Stefi]

=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:

 

[Ranjith Kurian]

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.

=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.

 

[T. Valko]

Maybe this:

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

--
Biff
Microsoft Excel MVP

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.

=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.