Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF, One criteria multiple columns
Here's what I need to do:
A B C D F G .........N O............ V Code va va va Code 12 L 520 250 M 13 L 500 700 M .. R 1080 250 LM .. COM 500 200 LM .. O 31 =SUMIF(A12:V31,"LM",G12:G31:N12:O31)/1000 this formula works with the criteria from column A =SUMIF(A12:A31,"L"... but it will not pull in the criteria from column V. What am I doing wrong? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF, One criteria multiple columns
O_o wrote:
Here's what I need to do: A B C D F G .........N O............ V Code va va va Code 12 L 520 250 M 13 L 500 700 M . R 1080 250 LM . COM 500 200 LM . O 31 =SUMIF(A12:V31,"LM",G12:G31:N12:O31)/1000 this formula works with the criteria from column A =SUMIF(A12:A31,"L"... but it will not pull in the criteria from column V. What am I doing wrong? You cannot test multiple coloumns with SUMIF. You can try something like this, if you want to sum columns G, N and O if both columns A *and* V satisfy the criteria: =SUMPRODUCT((A12:A31="LM")*(V12:V31="LM"),G12:G31, N12:N31,O12:O31)/1000 If, instead, you wnat to sum columns G, N and O if *or* column A *or* V *or* both satisfy the criteria you can use this formula: =SUMPRODUCT(ROUND(((A12:A31="LM")+(V12:V31="LM"))/2,0),G12:G31,N12:N31,O12:O31) -- (I'm not sure of names of menus, options and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF, One criteria multiple columns
Thanks for trying Franz but it didn't work :(
"Franz Verga" wrote: O_o wrote: Here's what I need to do: A B C D F G .........N O............ V Code va va va Code 12 L 520 250 M 13 L 500 700 M . R 1080 250 LM . COM 500 200 LM . O 31 =SUMIF(A12:V31,"LM",G12:G31:N12:O31)/1000 this formula works with the criteria from column A =SUMIF(A12:A31,"L"... but it will not pull in the criteria from column V. What am I doing wrong? You cannot test multiple coloumns with SUMIF. You can try something like this, if you want to sum columns G, N and O if both columns A *and* V satisfy the criteria: =SUMPRODUCT((A12:A31="LM")*(V12:V31="LM"),G12:G31, N12:N31,O12:O31)/1000 If, instead, you wnat to sum columns G, N and O if *or* column A *or* V *or* both satisfy the criteria you can use this formula: =SUMPRODUCT(ROUND(((A12:A31="LM")+(V12:V31="LM"))/2,0),G12:G31,N12:N31,O12:O31) -- (I'm not sure of names of menus, options and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF, One criteria multiple columns
=SUMPRODUCT(...,G12:G31,N12:N31,O12:O31)
gives the product, not the sum, of columns G, N and O |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF, One criteria multiple columns
O_o wrote:
Thanks for trying Franz but it didn't work :( Maybe it could be better if you explain us what are you trying to do... :-) -- (I'm not sure of names of menus, options and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Criteria for COUNTIF and SUMIF | Excel Worksheet Functions | |||
Multiple Criteria in a SUMIF formula | Excel Discussion (Misc queries) | |||
SUMIF, multiple criteria | Excel Discussion (Misc queries) | |||
SUMIF over multiple columns | Excel Worksheet Functions | |||
SumIf Function using multiple criteria | Excel Worksheet Functions |