@SUMIF formula with 2 conditions??

I have workbook with several sheets - On sheet "E" I want to total open orders listed on sheet "A" if Column E (on sheet A)(range name LOC) contains BRL and Column S (on sheet A)(range name OPEN) contains N; the amount is in Column M (on sheet A) (range name AMT). This is what I have tried @SUMIF ($LOC,BRL) + ($OPEN,"=N",$AMT) and I get ERR. How can I do this?

Thank you to anyone who has suggestion.
TootlesAsked:
Who is Participating?
 
patrickabConnect With a Mentor Commented:
Tootles,

The helper columns are D and R. In D1 I have put:

@IF(E1<>"BRL"#OR#E1="","",1)

this will put a 1 in column D if there's a BRL on that row in column E. I have named the data in column E,  LOC2 as a named range

And in R1 I have put:

@IF(S1<>"N"#OR#S1="","",1)

this will put a 1 in column R if there's an N on that row in column S. I have named the data in column R,  OPEN2 as a named range

and in both cases copy the formulae down the columns to the end of the data.

The amounts are a named range called AMT.

The named ranges, LOC2, OPEN2 and AMT all have the same number of rows - that's important.

In sheet E the formula is:

@SUMPRODUCT(LOC2,OPEN2,AMT)

On each row it mutliplies together the values it finds and adds the result to the running total. Let's take row 1 as an example. There's a 1 in column D and a 1 in column R and 1000 in column M. For that row SUMPRODUCT calculates  1 * 1 * 1000 = 1000 and adds that to the running total. SUMPRODUCT then looks at the next row. If there's a zero or a blank in columns D or R then the calculation produces for example 0 * 0 * 10 = 0 (zero) so it adds zero to the running total.

Hope all is now clear.

Patrick
0
 
patrickabCommented:
Tootles - Only just seen this. I'll be back soon... - Patrick
0
 
patrickabCommented:
TOOTLES,

Here's an example file using SUMPRODUCT():

http://www.asdy88.dsl.pipex.com/Experts%20Exchange/TOOTLES1.WK4

To use SUMPRODUCT() I have had to introduce some 'helper' columns which you can hide to keep them out of sight.

Hope that helps

Patrick
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
TootlesAuthor Commented:
Patrickab,

Appreciate your attempt to help; however, I could not open  the link you posted - Please post how I can access this solution.

Thanks,

Tootles
0
 
patrickabCommented:
Tootles,

It appears that my ISP's server will accept .wk4 file types but won't allow access to them. Storenow won't allow .wk4 file types at all so this file is zipped. Personally I dislike zipped files but this is the best I can do.

http://my.storenow.net?f=619

Meanwhile I will give details of what I have done and how I've done it in the next posting.

Hope that file helps

Patrick
0
 
patrickabCommented:
Oops - typos.

Tootles,

The helper columns are D and R. In D1 I have put:

@IF(E1<>"BRL"#OR#E1="","",1)

this will put a 1 in column D if there's a BRL on that row in column E. I have named the data in column D,  LOC2 as a named range

And in R1 I have put:

@IF(S1<>"N"#OR#S1="","",1)

this will put a 1 in column R if there's an N on that row in column S. I have named the data in column R,  OPEN2 as a named range

and in both cases copy the formulae down the columns to the end of the data.

The amounts are a named range called AMT.

The named ranges, LOC2, OPEN2 and AMT all have the same number of rows - that's important.

In sheet E the formula is:

@SUMPRODUCT(LOC2,OPEN2,AMT)

On each row it mutliplies together the values it finds and adds the result to the running total. Let's take row 1 as an example. There's a 1 in column D and a 1 in column R and 1000 in column M. For that row SUMPRODUCT calculates  1 * 1 * 1000 = 1000 and adds that to the running total. SUMPRODUCT then looks at the next row. If there's a zero or a blank in columns D or R then the calculation produces for example 0 * 0 * 10 = 0 (zero) so it adds zero to the running total.

Hope all is now clear.

Patrick
0
 
TootlesAuthor Commented:
Patrick:

Thank you, Thank you, Thank you!! This works great.  I am able to change range names and get totals for all my 20+ different locations.  The only thing I have to change is the AMT column - I actually designate exact row locations such as A:S10..A:S822 (in the @SUMPRODUCT formula) so the columns are exactly the same length.  Again thank you, this gives me the ability to do alot more in my spreadsheet.

Tootles
0
 
patrickabCommented:
Tootles,

It's a pleasure to help. Do please feel free to ask more questions. Meanwhile thanks for the grade.

By the way if you do ask further questions here may I suggest that after you have posted the question on EE, that you email me. The reason I ask is that I do not visit this area often and questons can be overlooked as a result. My email address is in my profile - you'll need to translate it to be able to use it! I know that sounds an odd request but the truth is there are few questions raised about Lotus123 so I tend not to look in every day.

To see my profile, click on my name (patrickab) on the left - my email address is at the end of my profile.

Patrick
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.