| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 .< |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 .< |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 .< |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 .< |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 .< |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 .< |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 .< |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multi Criteria Countif | Excel Worksheet Functions | |||
| if formula - multi criteria | Excel Worksheet Functions | |||
| multi-criteria retrievals | Excel Discussion (Misc queries) | |||
| Multi Criteria lookup | Excel Discussion (Misc queries) | |||
| Multi Criteria then sum | Excel Worksheet Functions | |||
Linear Mode
