Sum If function

R

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

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

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.



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

T. Valko

Maybe this:

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

--
Biff
Microsoft Excel MVP


Ranjith Kurian said:
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 said:
=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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top