How to use Sumif with multiple criteria to satisfy

Question

Level 1

18 points

How to use Sumif with multiple criteria to satisfy

Hello,

I am trying to create a function that adds together every number within a range of cells that satisfies two separate conditions. The first condition is that the category to the left of the number has to match a certain category, and the second condition is that the checkbox to the right of the number has to be checked (verses being left empty or blank). I've imagined that using the "sumif" function is the way to go but haven't figured out how to make it work. The full formula may need a combination of "and" "if" and "sumif" functions but i'm not sure. Can someone help me figure out a formula that will work? When applied to the set of values shown below, the formula should produce a value of $1,300 and should update to $2,300 automatically if the unchecked box in the first "income" row is checked.

MacBook Pro with Retina display, iOS 11.0.2

Posted on Oct 4, 2017 8:11 PM

Reply

Answer 1

Top-ranking reply

Barry

Level 9

60,048 points

Oct 8, 2017 4:54 PM in response to Kreggar

Hi Kreggar,

SUMIF accepts a single condition.

SUMIFS accepts one or more conditions. All must return TRUE for the associated value to be included in the sum.

The two images are of the same table, with different boxes checked in Column C.

The formula below the tables is shown as entered in the selected cell, E2.

The formula is filled down to E5.

SUMIFS(B,A,D2,C,TRUE)

Syntax:

SUMIFS(sum-values,test-values,condition,test-values,condition)

The conditions must be presented as text. In this case that is automatic, as the contents of cells in column D is already a text value, and the boolean value TRUE is recognized as the equivalent text value "TRUE". The comparison operator '=' is assumed if no operator is used in the condition statement.

Regards,

Barry

Reply

Answer 2

SGIII

Level 8

37,622 points

Oct 5, 2017 5:06 AM in response to Barry

Barry wrote:

The conditions must be presented as text.

Hi Barry,

You might want to refine this, as conditions often can be presented as numbers, not text. Here, column A is formatted as Number and the formula includes a number as the condition for that column.

I think the point you may have been trying to make is that when testing for inequalities, etc., one has to present the condition as a string (text), i.e. use "<1" not <1.

SG

Reply

Answer 3

Barry

Level 9

60,048 points

Oct 5, 2017 12:22 PM in response to SGIII

Hi SG,

Thanks for that clarification.

Regards,

Barry

Reply