SUMIFS (2 questions)

[Rudi]

Hi,

I have noticed that SUMIFS cannot have two criteria specified from the same text field.
IOW: SUMIFS(Salary, Dept_Range, "Sales", Dept_Range, "Admin") does not work.

1. Is there a way to make it work? (Bearing in mind that I do know of =DSUM(... or {=SUM(IF... array. Question focussed on SUMIFS...Tx)
2. Does SUMIFS only work on "AND scenarios" when it is applied to text data (ie: criteria over two or more columns). Is that why the question 1. does not work?

Just want to bounce this of the gurus to get confirmation.
TX

[HansV]

ALL criteria have to be met, whether they are text criteria or otherwise. In other words, SUMIF always ANDs the criteria.

You CAN use two criteria on the same range, but for a useful result, the criteria should not be exclusive. Examples:

=SUMIFS(Salary, LastName_Range, ">=K", LastName_Range, "<N")

if you want to add the salaries for employees whose last name begins with K, L or M.

=SUMIFS(Salary, Age_Range, ">30", Age_Range, "<=40")

if you want to add the salaries for employees between 31 and 40 years of age.

[Rudi]

Thanks Hans,

I do still find it tricky to understand that "Sales" and "Admin" are not exclusive.
They are two different criteria?
No different to your example of: =SUMIFS(Salary, LastName_Range, ">=K", LastName_Range, "<N")

I understand the numerical criteria, but the text based criteria down one column still eludes my comprehension.

[HansV]

Your example looks for departments that are Sales and Admin simulltaneously.
How could a department be Sales and Admin at the same time?

What you really want is Sales OR Admin. But SUMIFS doesn't do that. As you are well aware, there are other ways to accomplish that.

[Rudi]

Got it!!!

As simple as that! Once the penny drops...well, things become so simple!

Tx Hans!

[Rudi]

Hi,

Question: Are array functions less optimal that the built in functions of Excel?

Based on discovering that Excels SUMIFS function is limited only to "AND" conditions, I somehow like the flexibility of the array forms of functions similar to SUMIFS and COUNTIFS.

EG:
=SUMIFS(Salary, Region_Range, "North", Dept_Range, "Sales") ... only AND conditional

Alternative, using Array formulas:
{=SUM((Region_Range="North")*(Dept_Range="Sales)*Salary)} ... this is an AND conditional
{=SUM(((Region_Range="North")+(Region_Range="South"))*Salary)} ... this is an OR conditional

Tx

[HansV]

I think you're asking the wrong question.

The formula

{=SUM(((Region_Range="North")+(Region_Range="South"))*Salary)}

will only give a correct answer if the conditions (Region_Range="North") and (Region_Range="South") are mutually exclusive. If there are records that meet both conditions, they'll be counted twice. If the conditions are mutually exclusive, you can use

=SUMIF(Region_Range,"North",Salary)+SUMIF(Region_Range,"South",Salary)

Edit: Sorry, wrong example!

[Rudi]

Hi Hans,

In my test sheets, {=SUM(((Region_Range="North")+(Region_Range="South"))*Salary)} calculates perfectly??
It gives the same answer as, =SUMIF(Region_Range,"North",Salary)+SUMIF(Region_Range,"South",Salary).

The Region_Range is one column so there will never be records that meet both these conditions.

[HansV]

Sorry, I copied the wrong example. I meant to use

{=SUM(((Region_Range="North")+(Dept_Range="Sales))*Salary)}

This may not produce the correct result, if there is overlap between the two conditions. With mutually exclusive conditions, you can use two SUMIFS as well as the array formula.

[Rudi]

Sorry, I copied the wrong example. I meant to use

{=SUM(((Region_Range="North")+(Dept_Range="Sales))*Salary)}

With mutually exclusive conditions, you can use two SUMIFS as well as the array formula.

Hans,

Here is a sample workbook. Can you show me what you mean when you say: "This may not produce the correct result, if there is overlap between the two conditions." (I think I understand what you mean if there are more columns of criteria, but I just want to be sure!) TX

Again, the current workbook shows the two versions of the formula. (Help me to see the difference. (Sorry if I am a pest when it comes to this issue )

[HansV]

Your sample workbook uses two simple conditions on the same range. In that situation, the formulas will indeed return the same value. It's different if you use more complex conditions and/or different ranges. Please see my earlier examples.

[Rudi]

Your sample workbook uses two simple conditions on the same range. In that situation, the formulas will indeed return the same value. It's different if you use more complex conditions and/or different ranges. Please see my earlier examples.

This reply has clarified it again for me...TX
(I understand about more complex sheets, but I was under the impression that it was not good to do this for the simple example as in my attachment)

For example: In my workbook attachment, if I added a salesperson name and there happened to be a John in both "a" and "b", then this will cause errors.

Cheers