You are here:

# Excel/excel

Question
QUESTION: Hi,

I have a query on the Sumifs function within excel and hope that you can help. I will illustrate by a simple example below.

Column A       Column B
Row    Account No        Amount
1         212200          10
2         212300          5
3         218000          2
4         214000          20
5         215000          5
6         217000          8

Sum range =      column B (B1:B6)
Criteria Range = column a (A1:A6)

Criteria : >=212200, <=217000 but exclude 214000 to 215000. The answer should be 23.

How do I set this up in a formuale to give me the correct answer.

I looking forward to hearing from you.

Cheers,

dev

this worked for me.  =SUMIFS(B1:B6,A1:A6,">=212200",A1:A6,"<214000")+SUMIFS(B1:B6,A1:A6,">215000",A1:A6,"<=217000")

basically you have two AND conditions joined by an OR condition:

If I put this formula in C1 and drag fill down to C6 it will illustrate

=OR(AND(A1>=212200,A1<214000),AND(A1>215000,A1<=217000))

SUMIF only supports AND conditions so using two sumifs and adding their results will provide the functionality you describe.

--
Regards,
Tom Ogilvy

---------- FOLLOW-UP ----------

QUESTION: Thank you, Tom.

My thoughts were similar to yours and is a good solution for a simple problem like posed in my question. In reality, it could be more complex with many scenarios and exceptions and will lead to a very long formulae. Also I do not wish to use filters or pivot tables.

I have used atlas excel to extract information from Microsoft Dynamics and functionality there is impressive. In Atlas, part of the formulae that references the account reads like this(212200..217000,!214000..215000). The range is 212200 to 217000 but excludes (!) 214000 to 215000. Very simple and clear and easier to work with in a more complex reality.

Is there another formulae in excel (perhaps similar to atlas excel)that can be used ?. I am afraid I may have limited your initial answer by referring to my problem as SUMIFS query. Please expand your train of thought to include all possible solutions.

Look forward to hearing from you.

Cheers,

dev

dev,

I could us an array formula or I could probably use the old DSUM function.

You could use dummy columns such as I described for C1:C6 then use sumif against that column.

But just throw in a list of individual criteria and have the function sort it out - I am not aware of any built in worksheet function in excel that supports that.

--
Regards,
Tom Ogilvy

Questioner's Rating
 Rating(1-10) Knowledgeability = 10 Clarity of Response = 10 Politeness = 10 Comment Thank you, Tom

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

#### Tom Ogilvy

##### Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

##### Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.