| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct/SumIf | Excel Worksheet Functions | |||
| sumif-sumproduct | Excel Worksheet Functions | |||
| sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
| sumif and sumproduct together | Excel Discussion (Misc queries) | |||
| HELP!!!! sumif or sumproduct ??? :( | Excel Worksheet Functions | |||
Linear Mode
