|
SUMIF Formula Problem
Hi to all,
Wonder if you can help me. I have the following formula which works ok, is it possible to have a similar formula but with extended choice of cells: SUMIF('1'!A:A,A5,'1'!J:J) Example: The present formula will compare 1!A:A with A5, I would like a formula to compare 1!A:A,A5 and 1!B:B giving the same result from 1J:J 1!A:A being a category like Travel Expenses, 1!B:B being a department like Sales Department 1!J:J being the Cost Thank You Albert |
SUMIF Formula Problem
Hello.
If you are a XL2007 user, use SUMIFS, otherwise, give a try to : =SUMPRODUCT(('1'!A:A=A5)*('1'!B:B=A6)*'1'!J:J) HTH Daniel Hi to all, Wonder if you can help me. I have the following formula which works ok, is it possible to have a similar formula but with extended choice of cells: SUMIF('1'!A:A,A5,'1'!J:J) Example: The present formula will compare 1!A:A with A5, I would like a formula to compare 1!A:A,A5 and 1!B:B giving the same result from 1J:J 1!A:A being a category like Travel Expenses, 1!B:B being a department like Sales Department 1!J:J being the Cost Thank You Albert |
SUMIF Formula Problem
Try the following pattern:
=SUMPRODUCT(--(1!A:A="CRITERIA 1")*--(1!B:B="CRITERIA 2")*--(1!C:C="CRITERIA 3",1!J:J)) It would give you the total of amounts in J of the rows which have the cells in Column A, Column B AND Column C met. -- Best Regards, FARAZ A. QURESHI "albertmb" wrote: Hi to all, Wonder if you can help me. I have the following formula which works ok, is it possible to have a similar formula but with extended choice of cells: SUMIF('1'!A:A,A5,'1'!J:J) Example: The present formula will compare 1!A:A with A5, I would like a formula to compare 1!A:A,A5 and 1!B:B giving the same result from 1J:J 1!A:A being a category like Travel Expenses, 1!B:B being a department like Sales Department 1!J:J being the Cost Thank You Albert |
SUMIF Formula Problem
Thank you for your fast reply, but the formula is giving me the #NUM error.
Can it be I misunderstood something? Regards Albert "Daniel.C" wrote: Hello. If you are a XL2007 user, use SUMIFS, otherwise, give a try to : =SUMPRODUCT(('1'!A:A=A5)*('1'!B:B=A6)*'1'!J:J) HTH Daniel Hi to all, Wonder if you can help me. I have the following formula which works ok, is it possible to have a similar formula but with extended choice of cells: SUMIF('1'!A:A,A5,'1'!J:J) Example: The present formula will compare 1!A:A with A5, I would like a formula to compare 1!A:A,A5 and 1!B:B giving the same result from 1J:J 1!A:A being a category like Travel Expenses, 1!B:B being a department like Sales Department 1!J:J being the Cost Thank You Albert |
SUMIF Formula Problem
Thank you for your fast reply, but the formula is giving me the #NUM error.
Can it be I misunderstood something? Regards Albert "FARAZ QURESHI" wrote: Try the following pattern: =SUMPRODUCT(--(1!A:A="CRITERIA 1")*--(1!B:B="CRITERIA 2")*--(1!C:C="CRITERIA 3",1!J:J)) It would give you the total of amounts in J of the rows which have the cells in Column A, Column B AND Column C met. -- Best Regards, FARAZ A. QURESHI "albertmb" wrote: Hi to all, Wonder if you can help me. I have the following formula which works ok, is it possible to have a similar formula but with extended choice of cells: SUMIF('1'!A:A,A5,'1'!J:J) Example: The present formula will compare 1!A:A with A5, I would like a formula to compare 1!A:A,A5 and 1!B:B giving the same result from 1J:J 1!A:A being a category like Travel Expenses, 1!B:B being a department like Sales Department 1!J:J being the Cost Thank You Albert |
SUMIF Formula Problem
Thank you for your fast reply, but the formula is giving me the #NUM error.
Can it be I misunderstood something? Regards Albert "Daniel.C" wrote: Hello. If you are a XL2007 user, use SUMIFS, otherwise, give a try to : =SUMPRODUCT(('1'!A:A=A5)*('1'!B:B=A6)*'1'!J:J) HTH Daniel Hi to all, Wonder if you can help me. I have the following formula which works ok, is it possible to have a similar formula but with extended choice of cells: SUMIF('1'!A:A,A5,'1'!J:J) Example: The present formula will compare 1!A:A with A5, I would like a formula to compare 1!A:A,A5 and 1!B:B giving the same result from 1J:J 1!A:A being a category like Travel Expenses, 1!B:B being a department like Sales Department 1!J:J being the Cost Thank You Albert |
SUMIF Formula Problem
You can only use the whole column in xl2007. So adjust your ranges.
albertmb wrote: Thank you for your fast reply, but the formula is giving me the #NUM error. Can it be I misunderstood something? Regards Albert "Daniel.C" wrote: Hello. If you are a XL2007 user, use SUMIFS, otherwise, give a try to : =SUMPRODUCT(('1'!A:A=A5)*('1'!B:B=A6)*'1'!J:J) HTH Daniel Hi to all, Wonder if you can help me. I have the following formula which works ok, is it possible to have a similar formula but with extended choice of cells: SUMIF('1'!A:A,A5,'1'!J:J) Example: The present formula will compare 1!A:A with A5, I would like a formula to compare 1!A:A,A5 and 1!B:B giving the same result from 1J:J 1!A:A being a category like Travel Expenses, 1!B:B being a department like Sales Department 1!J:J being the Cost Thank You Albert -- Dave Peterson |
SUMIF Formula Problem
Sorry,
A Typo Mistake, consider the revised one. Only a place of closing parentesis changed. =SUMPRODUCT(--('1'!A:A="CRITERIA 1")*--('1'!B:B="CRITERIA 2")*--('1'!C:C="CRITERIA 3"),'1'!J:J) -- Best Regards, FARAZ A. QURESHI "albertmb" wrote: Thank you for your fast reply, but the formula is giving me the #NUM error. Can it be I misunderstood something? Regards Albert "FARAZ QURESHI" wrote: Try the following pattern: =SUMPRODUCT(--(1!A:A="CRITERIA 1")*--(1!B:B="CRITERIA 2")*--(1!C:C="CRITERIA 3",1!J:J)) It would give you the total of amounts in J of the rows which have the cells in Column A, Column B AND Column C met. -- Best Regards, FARAZ A. QURESHI "albertmb" wrote: Hi to all, Wonder if you can help me. I have the following formula which works ok, is it possible to have a similar formula but with extended choice of cells: SUMIF('1'!A:A,A5,'1'!J:J) Example: The present formula will compare 1!A:A with A5, I would like a formula to compare 1!A:A,A5 and 1!B:B giving the same result from 1J:J 1!A:A being a category like Travel Expenses, 1!B:B being a department like Sales Department 1!J:J being the Cost Thank You Albert |
SUMIF Formula Problem
Thank You Dave it worked perfectly
"Dave Peterson" wrote: You can only use the whole column in xl2007. So adjust your ranges. albertmb wrote: Thank you for your fast reply, but the formula is giving me the #NUM error. Can it be I misunderstood something? Regards Albert "Daniel.C" wrote: Hello. If you are a XL2007 user, use SUMIFS, otherwise, give a try to : =SUMPRODUCT(('1'!A:A=A5)*('1'!B:B=A6)*'1'!J:J) HTH Daniel Hi to all, Wonder if you can help me. I have the following formula which works ok, is it possible to have a similar formula but with extended choice of cells: SUMIF('1'!A:A,A5,'1'!J:J) Example: The present formula will compare â˜1â!A:A with A5, I would like a formula to compare â˜1â!A:A,A5 and â˜1â!B:B giving the same result from â˜1âJ:J â˜1â!A:A being a category like âœTravel Expensesâ, â˜1â!B:B being a department like âœSales Departmentâ â˜1â!J:J being the âœCostâ Thank You Albert -- Dave Peterson |
SUMIF Formula Problem
Thank You Faraz now it worked perfectly, but the actual problem was the
ranges, take note of what Dave Peterson remarked regarding non XL 2007 useres. Thank you once again for your interest "FARAZ QURESHI" wrote: Sorry, A Typo Mistake, consider the revised one. Only a place of closing parentesis changed. =SUMPRODUCT(--('1'!A:A="CRITERIA 1")*--('1'!B:B="CRITERIA 2")*--('1'!C:C="CRITERIA 3"),'1'!J:J) -- Best Regards, FARAZ A. QURESHI "albertmb" wrote: Thank you for your fast reply, but the formula is giving me the #NUM error. Can it be I misunderstood something? Regards Albert "FARAZ QURESHI" wrote: Try the following pattern: =SUMPRODUCT(--(1!A:A="CRITERIA 1")*--(1!B:B="CRITERIA 2")*--(1!C:C="CRITERIA 3",1!J:J)) It would give you the total of amounts in J of the rows which have the cells in Column A, Column B AND Column C met. -- Best Regards, FARAZ A. QURESHI "albertmb" wrote: Hi to all, Wonder if you can help me. I have the following formula which works ok, is it possible to have a similar formula but with extended choice of cells: SUMIF('1'!A:A,A5,'1'!J:J) Example: The present formula will compare 1!A:A with A5, I would like a formula to compare 1!A:A,A5 and 1!B:B giving the same result from 1J:J 1!A:A being a category like Travel Expenses, 1!B:B being a department like Sales Department 1!J:J being the Cost Thank You Albert |
| All times are GMT +1. The time now is 07:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com