Solved

@SUMIF formula with 2 conditions??

Posted on 2006-06-09
9
475 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
 
LVL 45

Expert Comment

by:patrickab
ID: 16888261
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now