sumproduct or sumif I need help, Thank you
 

In sheet 1, I have column A with accounts #, Column B amounts

Account # Amount
44000-100 10
45000-150 15
46000-100 10
46000-101 5
In Sheet 2, I have different departments with allocation of the accounts #
Column A Column B
Departmet A Department B
44000-100 45000-150
46000-101 46000-100

Sheet 2, will be updated continuosly, with new accounts or moving accounts
from 1 department to another or adding new departments
What I need to do is in Sheet 1 to have a summary by departmet as follow

Department A: 15

Department B: 25

I have tried giving a name to column A and B in Sheet 2, but I was not able
to get any results.
Thank you in advance for your response

sumproduct or sumif I need help, Thank you
 

Department A:
=SUMPRODUCT(--(NOT(ISERROR(MATCH($A$2:$A$5,Sheet2!$A:$A,0)))),$B $2:$B$5)

Department B:
=SUMPRODUCT(--(NOT(ISERROR(MATCH($A$2:$A$5,Sheet2!$B:$B,0)))),$B $2:$B$5)

Regards,
Stefi

€žEduardo€� ezt Ã*rta:

In sheet 1, I have column A with accounts #, Column B amounts

Account # Amount
44000-100 10
45000-150 15
46000-100 10
46000-101 5
In Sheet 2, I have different departments with allocation of the accounts #
Column A Column B
Departmet A Department B
44000-100 45000-150
46000-101 46000-100

Sheet 2, will be updated continuosly, with new accounts or moving accounts
from 1 department to another or adding new departments
What I need to do is in Sheet 1 to have a summary by departmet as follow

Department A: 15

Department B: 25

I have tried giving a name to column A and B in Sheet 2, but I was not able
to get any results.
Thank you in advance for your response

sumproduct or sumif I need help, Thank you
 

Thank you very much Stefi, that works like a dream

"Stefi" wrote:

Department A:
=SUMPRODUCT(--(NOT(ISERROR(MATCH($A$2:$A$5,Sheet2!$A:$A,0)))),$B $2:$B$5)

Department B:
=SUMPRODUCT(--(NOT(ISERROR(MATCH($A$2:$A$5,Sheet2!$B:$B,0)))),$B $2:$B$5)

Regards,
Stefi

€žEduardo€� ezt Ã*rta:

In sheet 1, I have column A with accounts #, Column B amounts

Account # Amount
44000-100 10
45000-150 15
46000-100 10
46000-101 5
In Sheet 2, I have different departments with allocation of the accounts #
Column A Column B
Departmet A Department B
44000-100 45000-150
46000-101 46000-100

Sheet 2, will be updated continuosly, with new accounts or moving accounts
from 1 department to another or adding new departments
What I need to do is in Sheet 1 to have a summary by departmet as follow

Department A: 15

Department B: 25

I have tried giving a name to column A and B in Sheet 2, but I was not able
to get any results.
Thank you in advance for your response

sumproduct or sumif I need help, Thank you
 

Thank you very much Stefi, that works like a dream

"Stefi" wrote:

Department A:
=SUMPRODUCT(--(NOT(ISERROR(MATCH($A$2:$A$5,Sheet2!$A:$A,0)))),$B $2:$B$5)

Department B:
=SUMPRODUCT(--(NOT(ISERROR(MATCH($A$2:$A$5,Sheet2!$B:$B,0)))),$B $2:$B$5)

Regards,
Stefi

€žEduardo€� ezt Ã*rta:

In sheet 1, I have column A with accounts #, Column B amounts

Account # Amount
44000-100 10
45000-150 15
46000-100 10
46000-101 5
In Sheet 2, I have different departments with allocation of the accounts #
Column A Column B
Departmet A Department B
44000-100 45000-150
46000-101 46000-100

Sheet 2, will be updated continuosly, with new accounts or moving accounts
from 1 department to another or adding new departments
What I need to do is in Sheet 1 to have a summary by departmet as follow

Department A: 15

Department B: 25

I have tried giving a name to column A and B in Sheet 2, but I was not able
to get any results.
Thank you in advance for your response

sumproduct or sumif I need help, Thank you
 

You are welcome! Thanks for the feedback!
Stefi

€žEduardo€� ezt Ã*rta:

Thank you very much Stefi, that works like a dream

"Stefi" wrote:

Department A:
=SUMPRODUCT(--(NOT(ISERROR(MATCH($A$2:$A$5,Sheet2!$A:$A,0)))),$B $2:$B$5)

Department B:
=SUMPRODUCT(--(NOT(ISERROR(MATCH($A$2:$A$5,Sheet2!$B:$B,0)))),$B $2:$B$5)

Regards,
Stefi

€žEduardo€� ezt Ã*rta:

In sheet 1, I have column A with accounts #, Column B amounts

Account # Amount
44000-100 10
45000-150 15
46000-100 10
46000-101 5
In Sheet 2, I have different departments with allocation of the accounts #
Column A Column B
Departmet A Department B
44000-100 45000-150
46000-101 46000-100

Sheet 2, will be updated continuosly, with new accounts or moving accounts
from 1 department to another or adding new departments
What I need to do is in Sheet 1 to have a summary by departmet as follow

Department A: 15

Department B: 25

I have tried giving a name to column A and B in Sheet 2, but I was not able
to get any results.
Thank you in advance for your response