SUMIF, One criteria multiple columns

[Guest]

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?

 

[Franz Verga]

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

 

[Guest]

Thanks for trying Franz but it didn't work

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

 

[Herbert Seidenberg]

=SUMPRODUCT(...,G12:G31,N12:N31,O12:O31)
gives the product, not the sum, of columns G, N and O

 

[Franz Verga]

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