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.
ANSWER: Hi 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
SUMIF only supports AND conditions so using two sumifs and adding their results will provide the functionality you describe.
---------- 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.
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.