Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 517
  • Last Modified:

@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.
0
Tootles
Asked:
Tootles
  • 7
  • 2
1 Solution
 
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
 
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
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.

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now