Solved

@SUMIF formula with 2 conditions??

Posted on 2006-06-09
9
477 Views
Last Modified: 2010-04-22
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.
0
Comment
Question by:Tootles
  • 7
  • 2
9 Comments
 
LVL 45

Expert Comment

by:patrickab
ID: 16876743
Tootles - Only just seen this. I'll be back soon... - Patrick
0
 
LVL 45

Expert Comment

by:patrickab
ID: 16877058
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
 

Author Comment

by:Tootles
ID: 16887051
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 45

Expert Comment

by:patrickab
ID: 16888261
0
 
LVL 45

Expert Comment

by:patrickab
ID: 16888322
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
 
LVL 45

Accepted Solution

by:
patrickab earned 500 total points
ID: 16888474
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
 
LVL 45

Expert Comment

by:patrickab
ID: 16888500
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
 

Author Comment

by:Tootles
ID: 16895369
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
 
LVL 45

Expert Comment

by:patrickab
ID: 16897164
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

Featured Post

How our DevOps Team Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question