Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

@SUMIF formula with 2 conditions??

Posted on 2006-06-09
9
Medium Priority
?
504 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

  In today’s Arena we can’t imagine our lives without Internet as we are highly used to of it. If we consider our life style just for only 2 min we found that face to face communication is swapped by e-communication.  Every Where from Works place to…
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…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

598 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