|
sum with multi criteria
I Have 3 columns with like 800 rows
column #1 is the year , column #2 is Codenumder , column #3 is the cost year code cost #1 #2 #3 2003 80 500 2001 10 100 2003 230 1000 2002 80 50 2006 10 1500 2003 80 1000 i need to get the cost of the year 2003 that has a codenumber 80 wich here if i do it with my eyes its 1500 how can i make a function for it , to do it for me , even a macro if needed .< |
sum with multi criteria
=SUMPRODUCT(--(A2:A200=2003),--(B2:B200=80),C2:C200)
-- --- HTH Bob (change the xxxx to gmail if mailing direct) "azoz" wrote in message ... I Have 3 columns with like 800 rows column #1 is the year , column #2 is Codenumder , column #3 is the cost year code cost #1 #2 #3 2003 80 500 2001 10 100 2003 230 1000 2002 80 50 2006 10 1500 2003 80 1000 i need to get the cost of the year 2003 that has a codenumber 80 wich here if i do it with my eyes its 1500 how can i make a function for it , to do it for me , even a macro if needed .< |
sum with multi criteria
excel says there is an error in formula
i did it even with ctrl+shift+enter "wich never works for me" ========= "Bob Phillips" wrote: =SUMPRODUCT(--(A2:A200=2003),--(B2:B200=80),C2:C200) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "azoz" wrote in message ... I Have 3 columns with like 800 rows column #1 is the year , column #2 is Codenumder , column #3 is the cost year code cost #1 #2 #3 2003 80 500 2001 10 100 2003 230 1000 2002 80 50 2006 10 1500 2003 80 1000 i need to get the cost of the year 2003 that has a codenumber 80 wich here if i do it with my eyes its 1500 how can i make a function for it , to do it for me , even a macro if needed .< |
sum with multi criteria
It works fine, and doesn't need to be array entered. Perhaps you have errors
in the data being evaluated. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "azoz" wrote in message ... excel says there is an error in formula i did it even with ctrl+shift+enter "wich never works for me" ========= "Bob Phillips" wrote: =SUMPRODUCT(--(A2:A200=2003),--(B2:B200=80),C2:C200) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "azoz" wrote in message ... I Have 3 columns with like 800 rows column #1 is the year , column #2 is Codenumder , column #3 is the cost year code cost #1 #2 #3 2003 80 500 2001 10 100 2003 230 1000 2002 80 50 2006 10 1500 2003 80 1000 i need to get the cost of the year 2003 that has a codenumber 80 wich here if i do it with my eyes its 1500 how can i make a function for it , to do it for me , even a macro if needed .< |
sum with multi criteria
Re-check your formula. Bob's formula is correct and works for me. Copy &
paste from your news reader if necessary. That is how I did it. Mike F "azoz" wrote in message ... excel says there is an error in formula i did it even with ctrl+shift+enter "wich never works for me" ========= "Bob Phillips" wrote: =SUMPRODUCT(--(A2:A200=2003),--(B2:B200=80),C2:C200) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "azoz" wrote in message ... I Have 3 columns with like 800 rows column #1 is the year , column #2 is Codenumder , column #3 is the cost year code cost #1 #2 #3 2003 80 500 2001 10 100 2003 230 1000 2002 80 50 2006 10 1500 2003 80 1000 i need to get the cost of the year 2003 that has a codenumber 80 wich here if i do it with my eyes its 1500 how can i make a function for it , to do it for me , even a macro if needed .< |
sum with multi criteria
ok it worked on my office 2002 at home
but in work i have excel 2003 and it wont work there ========== "Bob Phillips" wrote: It works fine, and doesn't need to be array entered. Perhaps you have errors in the data being evaluated. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "azoz" wrote in message ... excel says there is an error in formula i did it even with ctrl+shift+enter "wich never works for me" ========= "Bob Phillips" wrote: =SUMPRODUCT(--(A2:A200=2003),--(B2:B200=80),C2:C200) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "azoz" wrote in message ... I Have 3 columns with like 800 rows column #1 is the year , column #2 is Codenumder , column #3 is the cost year code cost #1 #2 #3 2003 80 500 2001 10 100 2003 230 1000 2002 80 50 2006 10 1500 2003 80 1000 i need to get the cost of the year 2003 that has a codenumber 80 wich here if i do it with my eyes its 1500 how can i make a function for it , to do it for me , even a macro if needed .< |
sum with multi criteria
Maybe your work computer is set up to use semi-colons in formulas,
instead of commas: =SUMPRODUCT(--(A2:A200=2003);--(B2:B200=80);C2:C200) azoz wrote: ok it worked on my office 2002 at home but in work i have excel 2003 and it wont work there ========== "Bob Phillips" wrote: It works fine, and doesn't need to be array entered. Perhaps you have errors in the data being evaluated. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "azoz" wrote in message ... excel says there is an error in formula i did it even with ctrl+shift+enter "wich never works for me" ========= "Bob Phillips" wrote: =SUMPRODUCT(--(A2:A200=2003),--(B2:B200=80),C2:C200) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "azoz" wrote in message ... I Have 3 columns with like 800 rows column #1 is the year , column #2 is Codenumder , column #3 is the cost year code cost #1 #2 #3 2003 80 500 2001 10 100 2003 230 1000 2002 80 50 2006 10 1500 2003 80 1000 i need to get the cost of the year 2003 that has a codenumber 80 wich here if i do it with my eyes its 1500 how can i make a function for it , to do it for me , even a macro if needed .< -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
sum with multi criteria
In what way did it not work?
-- --- HTH Bob (change the xxxx to gmail if mailing direct) "azoz" wrote in message ... ok it worked on my office 2002 at home but in work i have excel 2003 and it wont work there ========== "Bob Phillips" wrote: It works fine, and doesn't need to be array entered. Perhaps you have errors in the data being evaluated. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "azoz" wrote in message ... excel says there is an error in formula i did it even with ctrl+shift+enter "wich never works for me" ========= "Bob Phillips" wrote: =SUMPRODUCT(--(A2:A200=2003),--(B2:B200=80),C2:C200) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "azoz" wrote in message ... I Have 3 columns with like 800 rows column #1 is the year , column #2 is Codenumder , column #3 is the cost year code cost #1 #2 #3 2003 80 500 2001 10 100 2003 230 1000 2002 80 50 2006 10 1500 2003 80 1000 i need to get the cost of the year 2003 that has a codenumber 80 wich here if i do it with my eyes its 1500 how can i make a function for it , to do it for me , even a macro if needed .< |
| All times are GMT +1. The time now is 10:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com